SQL Server offers a plethora of string functions that empower developers to manipulate and transform text data efficiently. In this comprehensive guide, we will explore various string functions, unraveling their functionalities with examples.
1. CHARINDEX
The CHARINDEX function retrieves the index of a specified character within a given string. The syntax is as follows:
`SELECT CHARINDEX('S', 'MICROSOFT SQL SERVER 2000') as Result;
Result: 6
2. LEFT
The LEFT function extracts a specified number of characters from the left side of a string. Here’s how it works:
`SELECT LEFT('MICROSOFT SQL SERVER 2000', 4) as Result;
Result: MICR
3. RIGHT
Conversely, the RIGHT function extracts characters from the right side of a string:
`SELECT RIGHT('MICROSOFT SQL SERVER 2000', 4) as Result;
Result: 2000
4. LEN
To determine the length of a string, the LEN function comes in handy:
`SELECT LEN('MICROSOFT SQL SERVER 2000') as Result;
Result: 25
5. REPLACE
The REPLACE function replaces a specified portion of a string with a new value:
`SELECT REPLACE('MICROSOFT SQL SERVER 2000', 'MICROSOFT', 'MS') as Result;
Result: MS SQL SERVER 2000
6. STUFF
Using the STUFF function, you can replace a specified length of characters with a new pattern:
`SELECT STUFF('MICROSOFT SQL SERVER 2000', 11, 3, 'S.Q.L.') as Result;`
Result: MICROSFT S.Q.L. SERVER 2000
7. SUBSTRING
The SUBSTRING function extracts a substring of specified length starting from a given position:
`SELECT SUBSTRING('MICROSOFT SQL SERVER 2000', 11, 3) as Result;
Result: SQL
8. LOWER
Convert a string to lowercase using the LOWER function:
`SELECT LOWER('MICROSOFT ASP .NET WEB HOSTING') as Result;
Result: microsoft asp .net web hosting
9. UPPER
Similarly, the UPPER function converts a string to uppercase:
`SELECT UPPER('MICROSOFT ASP .NET WEB HOSTING with SQL Database') as Result;
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE
10. REVERSE
REVERSE reverses the characters in a string:
`SELECT REVERSE('ASP.NET') as Result;
Result: TEN.PSA
11. LTRIM
Remove leading spaces with the LTRIM function:
`SELECT LTRIM(' ASP ') as Result;
Result: ASP----- (Blanks at the right side not removed)
12. RTRIM
Remove trailing spaces with the RTRIM function:
`SELECT RTRIM(' ASP ') as Result;`
Result: -----ASP (Blanks at the left side not removed)
13. PATINDEX
PATINDEX returns the position of the first occurrence of a specified pattern:
`SELECT PATINDEX('%RO%', 'MICROSOFT') as Result;`
Result: 4
14. STR
Convert numeric data to character data using the STR function:
`SELECT STR(140.15, 6, 1) as Result;`
Result: 140.2
15. ASCII
Retrieve the ASCII code value of a character:
`SELECT ASCII('A') as Result;`
Result: 65
These string functions are powerful tools in a SQL developer’s arsenal, enabling precise manipulation and extraction of information from text data. Incorporate them wisely into your queries to enhance the versatility of your SQL skills. Happy querying!
Comments
Select Replace('family name', ' ', '');
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! :)
????
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)
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
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'
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)
eg microsoft sql server
result: mss
you can try this
select right(left('NWST/330/23/WT6', 8 ),3)
to get 330 out of the string..