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')

SUBSTRING 

SUBSTRING(textData, startPoint,length)

--return portion of the string

SELECT SUBSTRING('Rishi Kumar',3,5)

UPPER

UPPER(textData)

--convert all text to UPPER Case

SELECT UPPER('rishi sanuj')

backup all SQL Server databases

--script to backup all databases

DECLARE @Name VARCHAR(50)
DECLARE @Path VARCHAR(256)
DECLARE @Filename VARCHAR(256)
DECLARE @FileDate VARCHAR(20)

SET @Path='c:\Backup\'
SELECT @FileDate=CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_Cursor CUrSOR FOR
SELECT Name FROM Master.dbo.sysdatabases WHERE Name NOT IN ('master','model','msdb','tempdb') 

OPEN db_Cursor
Fetch NEXT FROM db_Cursor INTO @name

WHILE @@FETCH_STATUS=0
BEGIN 
SET @Filename=@Path+@Name+'_'+@FileDate+'.bak'
backup database @name TO DISK =@Filename
FETCH NEXT FROM db_Cursor INTO @Name
END
CLOSE db_Cursor
DEALLOCATE db_Cursor