SQL Server String processing functions

String date Formatting Function
Create   Function Fn_datetostring ( @ Date   Datetime , @ Format   Varchar ( 20 ))
Returns   Varchar ( 20 )
As
Begin
Declare   @ Result   Varchar ( 20 )
Select   @ Result   = ( Replace ( Replace ( Replace ( @ Format , ' Yyyy ' , ' 20 ' + Substring ( Convert ( Char ( 8 ), @ Date , 3 ), 7 , 2 )), ' Mm ' , Substring ( Convert ( Char ( 8 ), @ Date , 3 ), 4 , 2 )), ' Dd ' , Substring ( Convert ( Char ( 8 ), @ Date , 3 ), 1 , 2 )))
Return   @ Result
End

Usage:

Select DBO. fn_datetostring ( Getdate (), ' Yyyy-mm-dd ' )

You can also use covert (varchar (20), getdate (), 120)

Datalength (expression)
 
Returns the number of bytes used to represent any expression.


 
 
Set textsize 0 set nocount on -- create the variables for the current character string position -- and for the character string. declare @ position int, @ string char (15) -- initialize the variables. set @ position = 1 Set @ string = 'du monde entier 'while @ position <= datalength (@ string) beginselect ASCII (substring (@ string, @ position, 1 )), char (ASCII (substring (@ string, @ position, 1) set @ position = @ position + 1 endset nocount offgo
 
 
 
 
 
 
 
Charindex (expression1, expression2 [, start_location])
 
 
If either expression1 or expression2 is of the Unicode data type (nvarchar or nchar), the other is converted to the Unicode data type. Charindex cannot be used with the Data Types of text, ntext, and image.
 
 
If either expression1 or expression2 is null and the database compatibility level is 70 or higher, charindex returns NULL. If the database compatibility level is 65 or lower, charindex returns NULL only when both expression1 and expression2 are null.
 
 
If expression2 is not found in expression1, charindex returns 0.
 
 
 
 
 
 
 
 
 
 
 
Replace ('string _ expression1', 'string _ expression2', 'string _ expression3 ')
 
 
Select Replace ('abcdefghicde', 'cde', 'xxx'); Result: abxxxfghixxx
 
 
 
 
 
English Similarity comparison
 
Use adventureworks; go -- returns a difference value of 4, the least possible difference. select soundex ('green'), soundex ('greene '), difference ('green', 'greene'); go -- returns a difference value of 0, the highest possible difference. select soundex ('blotchet-Hall'), soundex ('greene'), difference ('blotchet-Hall', 'greene'); go
 
 
 
 
 
 
 
 
In the following exampleAbcdefFrom2Location (characterB), And then insert the second string at the start of the deletion to create and return a string.
 


         
      


          
       
 
        
 
         
 
 
         
  copy Code  
 
        
 
        
 
         
 
 select stuff ('abcdef', 2, 3, 'ijklmn ') Go 
 
 
        
 
       

         
      
 
 left (character_expression, integer_expression) 
 
 right (character_expression, integer_expression) 
 
 replicate (character_expression, integer_expression) expression with the specified number of repeated characters 
 
 substring (expression, start, length) 
 
 Len (string_expression) 
 
 reverse (character_expression)