Dot Net Point: Text Data Manipulation in SQL Server

Text Data Manipulation in SQL Server

CHARINDEX

CHARINDEX(findTextData, textData,[StartingPosition])

- Returns the specific postion of the specified expression in a character string
- Starting Postion is optional

SELECT CHARINDEX('s','Rishi Sanuj')
SELECT CHARINDEX('s','RishiSanuj',5)

LEFT

- LEFT(character_expression, integer_expression)
- Returns the left part of the character string with the specified number of character

SELECT LEFT('Sushant Priyadarshi',6)
SELECT LEFT('Sushant Priyadarshi',12)

LEN

LEN(textData)
- return the value of length of the string

SELECT LEN('Rishi')
SELECT LEN('Sushant Priyadarshi')

LOWER

LOWER(character_expression)

- return a character expression after converting upper case character data to lower case

SELECT LOWER('RISHI SANUJ')

LTRIM

LTRIM(textData)

- Remove leading blanks in string

SELECT LTRIM(' Rishi Sanuj')

PATINDEX

PATINDEX(findTextData,textData)

--returns the position of starting data of string

SELECT PATINDEX('%sa%', 'Rishi kumar Sanuj')

REPLACE

REPLACE(textData,findTextData,replaceWithTextdata)

--Replace the text found in occurance with new data

SELECT REPLACE('Rishi KUMAR Sanuj','ishi','aj')

REVERSE

REVERSE(character_expression)

--returns the reverse character expression

SELECT REVERSE('Rishi')

REPLICATE

-REPLICATE(character_expression,integer_expression)

- Repeate a character for a specific number of time

SELECT REPLICATE(' Rishi kumar',5)

RTRIM

RTRIM(textData)

-- remove trailing blanks

SELECT RTRIM('Rishi ')

SPACES

SPACES(number of spaces)

- repeats space values a number of times

SELECT 'Rishi'+SPACE(5)+'Sanuj'

STUFF

STUFF(textData,start, length,insertTextData)

-- DELETE a specified number of length of character and insert another set of character at a specified starting Point

SELECT STUFF('RISHI KUMAR SANUJ',1,5,'Sushant')