Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo

SQL: String Functions

form ascii value string

  • Please log in to reply
2 replies to this topic

#1 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 02 September 2009 - 03:39 PM

SQL Functions - String Functions

SQL provides a large amount of functions for string manipulations.

ASCII

The ASCII function is used to return a numeric value for the leftmost character. Each character has a numeric value that represents it.

Example:

SELECT ASCII('A')

Output:

[QUOTE]
65
[/QUOTE]

65 is the numeric representation. An interesting property of characters is that each letter is one integer value away. This makes it great for adding and subtracting other values. You can perform math with characters.

So:

SELECT CHAR(ASCII('A')+1)

Returns B. This converts A to it's ascii value and adds 1. Then it converts it back to a character. This shows that the letters are exactly one integer value away.

Thus we can use this method to convert chars from upper to lowercase by observing patterns in an ASCII chart.

SELECT CHAR(ASCII('A')+ASCII('a')-ASCII('A'))

This code converts A to lowercase "a".

Character Length

The character length function returns the number of letters in the string.

SELECT CHARACTER_LENGTH("Hiii")

This function returns the number of characters in the string.

Output:

[QUOTE]
4
[/QUOTE]

Another method that does the same thing is CHAR_LENGTH.

CONCAT method

This method is used to join strings together.

SELECT CONCAT("James","Hi","There");

The function takes a variable number of arguments and joins them together.

The returning value is:

[QUOTE]
Jame****here
[/QUOTE]

Format Function

Returns a number formatted to a certain amount of decimals. The function prototype is:

[QUOTE]
FORMAT(num,D)
[/QUOTE]

The number num is formatted in a format similar to '#,###.##'. The number of decimals is D values.

Example:

SELECT FORMAT(1234.343332,3)

Output:

[QUOTE]
1,234.343
[/QUOTE]

Lowercase Functions

The function LCASE returns a string in lower case. This function does the same thing as the LOWER function.

Example:

SELECT LOWER("Test");

Output:

[QUOTE]
test
[/QUOTE]

Trim Functions

There are three functions:

  • LTRIM
  • RTRIM
  • TRIM

that remove the space from around the string. The LTRIM function removes all leading spaces before the left side of the string. The RTRIM function removes all trailing spaces after the right side of the string. The TRIM function removes all leading and trailing spaces.

Examples:

SELECT LTRIM("      Test");
SELECT RTRIM("Test       ");
SELECT TRIM("       Test      ");
SELECT RTRIM("         Test          ");

Output:

[QUOTE]
Test
Test
Test
Test
[/QUOTE]

Replace

The generic function is:

REPLACE(str,replace,new)

The replace function replaces all instances of a string replace with new.

Example:

SELECT REPLACE("Test  this is just a test test test","test","yo!");

The output:

[QUOTE]
Test this is just a yo! yo! yo!
[/QUOTE]

All instances of "this" will be replaced with "yo". This method is case-sensitive. Notice, how Test was not replaced?

Reverse Function

This function returns a string that is reversed.

Example:

SELECT REVERSE("test");

The output is:

[QUOTE]
tset
[/QUOTE]

Substring Function

There are two versions of this function:

SUBSTR(str,pos)

and

SUBSTR(str,pos,len)

The first function copies all the characters from index pos to the end of the string. One thing to note, the index of the first character is one.

Example:

SELECT SUBSTR("test",2);

Output:

[QUOTE]
est
[/QUOTE]

The second version specifies how many characters to copy and where to start copying from.

Example:

SELECT SUBSTR("test",1,2);

This function starts at the first character and copies two characters.

Output:

[QUOTE]
te
[/QUOTE]

Uppercase Function

The UPPER function converts a string into upper case.

Example:

SELECT UPPER("test");

Output:

[QUOTE]
TEST
[/QUOTE]
  • 2

#2 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 03 September 2009 - 04:07 AM

Nice little tutorial. +rep!
  • 0

#3 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 03 September 2009 - 04:52 AM

Very handy list of functions. I use them a LOT. +rep
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/