Saturday, March 29, 2008

SQL String Functions Tutorial

1. CHARINDEX string function takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.

Example:
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6


2. LEFT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.

Example:
Select LEFT ('MICROSOFT SQL SERVER 2000',4)
Result: MICR


3. RIGHT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.

Example:
Select RIGHT ('MICROSOFT SQL SERVER 2000',4)
Result: 2000


4. LEN string function takes 1 argument as string value and returns the length of entered string.

Example:
Select LEN ('MICROSOFT SQL SERVER 2000')
Result: 25


5. REPLACE string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string entered as 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.

Example:
Select REPLACE ('MICROSOFT SQL SERVER 2000','MICROSOFT','MS')
Result: MS SQL SERVER 2000


6. STUFF string function takes 4 arguments. It is used to replace specified length of characters with provided pattern.
1st argument as string value.
2nd argument as integer value specifying the starting point of characters to be replaced.
3rd arguments as integer value specifying the length of characters.
4th argument as string value specifying the new pattern of characters.

Example:
Select STUFF ('MICROSOFT SQL SERVER 2000', 11, 3,'S.Q.L.')
Result: MICROSFT S.Q.L. SERVER 2000


7. SUBSTRING string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length

Example:
Select SUBSTRING ('MICROSOFT SQL SERVER 2000', 11, 3)
Result: SQL

8. LOWER string function returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING’)
Result: microsoft asp .net web hosting


9. UPPER string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

10. REVERSE string function returns the string in reverse order. It takes 1 argument as string value.

Example:
select REVERSE(‘ASP.NET’)
Result: TEN.PSA


11. LTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select LTRIM (‘ ASP ’)
Result: ASP-----
blanks at the right side not removed.

12. RTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select RTRIM (‘ ASP ’)
Result: -----ASP
blanks at the left side not removed.


13. PATINDEX function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.

Example:
select PATINDEX('%RO%','MICROSOFT')
Results: 4


14. STR function returns character data converted from numeric data. It takes 3 arguments.
1st argument as float data
2nd argument as integer value specifying the length of the string including decimal that is to be retrieved.
3rd argument as integer specifying the number of places to the right of the decimal point.

Example:
select STR(140.15, 6, 1)
Result: 140.2


15. ASCII function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.

Example:
select ASCII('A')
Result: 65

22 comments:

Gaurav said...

Thanks for the awesome post!

Ka Kagwang said...

how about ignoring spaces? like "family name" to "familyname" :)

Davinder Singh said...

About ignoring spaces? like "family name" to "familyname" :)

Select Replace('family name', ' ', '');

Linda said...

How about "FamilyName" to "Family Name"

Chandrashekhar Mahale said...
This comment has been removed by the author.
smita said...

How to find name such as its second character is a.

tolstory said...

how do i chose the middle section of a string when the desired section is surrounded by the same character "/" and the start of the string is not always at the same index...

e.g. "NWST/330/23/WT6" to "330"
and "NTW/1010/43/TY7" to "1010"
and "TYQT/99/WYT3" to "99"

I have tried so may things including your list of SQL functions, and even InStr and InStre, Mid... Please HELP! :)
????

Hari said...
This comment has been removed by the author.
Hari said...
This comment has been removed by the author.
Hari said...

To get 330 from 'NWST/330/23/WT6', use the following code:

declare @a varchar(50)
Set @a = Right('NWST/330/23/WT6', Len('NWST/330/23/WT6') - CharIndex('/', 'NWST/330/23/WT6'))

select Left(@a, charIndex('/', @a) - 1)

vishwanath said...

@Linda SELECT replace('FamilyName','Family','Family ');

vishwanath said...
This comment has been removed by the author.
vishwanath said...
This comment has been removed by the author.
DuraiPandy said...
This comment has been removed by the author.
DuraiPandy said...

Thanks Very Useful for Post.

In That Function Example Syntax Was Wrong ..Pleas Make it Correct

9. UPPER string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

It Should Be
Example:
select UPPER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

naga mahesh said...

http://incomepart.com/ref.php?page=act/ref&invcod=113618

Mahen said...

In this string "Exhibition", how can i return count of i's in this string

Sudip Guti said...

@Mahen Check this


Create procedure CountChar
(
@Char2bCount varchar(1),
@Inputstring varchar(100)
)
as
begin
declare @count int
declare @countend int
declare @occurance int
set @occurance=0
set @count=0
set @countend=LEN(@Inputstring)
while(@count<=@countend)
begin
if(SUBSTRING(@Inputstring,@count,1)=@Char2bCount)
begin
set @occurance=@occurance+1;
end
set @count=@count+1;
end
print 'Number of Occurance of character "'+@Char2bCount+'" in the string "'+@Inputstring+'" is '+ Cast(@Occurance as varchar)
end

exec CountChar 's','SudipGuti'

Harry Panesar said...

This will work to pull 1st instance of the string within the '\'..


DECLARE @S VARCHAR(100)
SET @S = 'MICRO\SOFT\SQL\SERVER 2000'
SELECT SUBSTRING(@S,CHARINDEX('\',@S) + 1,CHARINDEX('\',@S, CHARINDEX('\',@S) + 1 ) - CHARINDEX('\',@S) -1)

Raviteja Dandu said...

how do Replacing first occurrence of certain characters in sql 2008 database

diwaker dutta said...

how to get the initials of the strings
eg microsoft sql server
result: mss

diwaker dutta said...

@tolstory

you can try this
select right(left('NWST/330/23/WT6', 8 ),3)
to get 330 out of the string..