String Functions in Sql Using Example

// Posted By Suresh


Declare @str varchar(50), @str1 varchar(50)
set @str = 'Suresh'
set @str1 = 'Jangid'


--Convert In Upper case
Select UPPER(@str) -- OUTPUT >> SURESH


--Convert In Lower case
Select LOWER(@str) -- OUTPUT >> suresh


--Add Space Between Two Strings
Select @str + SPACE(1) + @str1 --OUTPUT >> Suresh Jangid


--Reverse From any Sting
Select REVERSE(UPPER(@str)) -- OUTPUT >> HSERUS

Select CHARINDEX('r',@str) -- OUTPUT >> 3


--Char to Ascii
Select ASCII('A') --OUTPUT >> 65


--Character Find Out From String Non Case Sensitive
Select LEN(@str)-LEN(REPLACE(@str,'s',''))-- OUTPUT >> 2


--Character Find Out From String Case Sensitive
Select LEN(@str COLLATE SQL_Latin1_General_Cp1_CS_AS)-LEN(REPLACE(@str COLLATE SQL_Latin1_General_Cp1_CS_AS,'s' COLLATE SQL_Latin1_General_Cp1_CS_AS,''))-- OUTPUT >> 1


--ASCII TO Char Conversion
Select Nchar(65) --OUTPUT >> A


Select UNICODE('A') --OUTPUT >> 65


--Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types
SELECT PATINDEX ( '%s%', @str) --OUTPUT >> 1


--The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position
Select STUFF(@str,2,2,@str1) --OUTPUT >> SJangidesh


--Returns the left part of a character string with the specified number of characters.
Select LEFT(@str,3) --OUTPUT >>Sur


--Returns the Right part of a character string with the specified number of characters
Select RIGHT(@str,3) --OUTPUT >> esh


--Repeats a string value a specified number of times
Select REPLICATE(@str,2)--OUTPUT >> SureshSuresh


--Returns part of a character, binary, text, or image expression
Select SUBSTRING(@str,3,LEN(@str)) --OutPut >> resh


--Returns a character expression after it removes leading blanks From Left
Select LTRIM(' '+@str)


--Returns a character expression after it removes leading blanks From Right
Select RTRIM(@str+' ')


--Replaces all occurrences of a specified string value with another string value.
Select REPLACE(@str,'s','x') -- OutPut >> xurexh