Summary of some common functions in SQL Server

Remove whitespace

LTRIM (' content '): Remove the space to the left of the string

RTRIM (' content '): Remove the space on the right

LTRIM (RTRIM (' content ')): Remove the left and right spaces of the string

REPLACE (' content ', ' ', '): Remove all the spaces in the string

Find replacement aspects

CHARINDEX (' characters to find ', ' What to Look for '): If you're looking for something you don't want to find, return 0, find the location where the return string is located, and the index starts at 1

Left (' string to intercept ', ' intercept length '): Intercept from

Right (' string to intercept ', ' intercept length '): Intercept from the left

PATINDEX ('%[^a-za-z.-]% ', ' content '), looking from the left, returns the location where the regular table appears in the content, not found return 0

ISNULL (' content ', '): Determine if the string is null, or return '

COALESCE (' parameter 1 ', ' Parameter 2 '): Returns parameter 1 if the value of parameter 1 is not NULL, otherwise returns the value of parameter 2

ISDATE (' content '): Determines whether it is a time format, returns 1 if it is, no words return 0

Case-When usage

SELECT time = Case ISDATE (' 2015-1-1 ')

When 1 Then ' time format is correct '

ELSE ' non-standard Time format ' END

Case-when multi-conditional judgment usage

SELECT content = Case

When the ' 1232 ' like '%1% ' then ' 1 '

When the ' 1232 ' like '%2% ' then ' 2 '

When the ' 1232 ' like '%3% ' then ' 3 '

ELSE NULL END

Case-When-embedded method

SELECT

Case CHARINDEX ('/', time 1)
When 0 then NULL

ELSE

Case
When convert (Int,left (time)) >=month (GETDATE ()) then CONVERT (VARCHAR (Ten), Year (GETDATE ())) + '-' +replace (time 1, '/', ‘-‘)

ELSE CONVERT (VARCHAR), Year (GETDATE ()) +1) + '-' +replace (time 1, '/', '-')

End end as port time,

Summary of some common functions in SQL Server