IT:AD:SQL Server:HowTo:SQL:Functions/String Functions

Easily – as long as you remember a couple of points:

• stick with vartypes that can implicitly be converted to varchar or nvarchar (text and ntext values need to be CONVERTed first).
• They are 1-indexed (unlike C#).
• DATALENGTH returns the length of the string in bytes, including trailing. spaces. LEN returns the length in characters, excluding trailing spaces
• Amazingly enough, there is no TRIM – you have to LTRIM(RTRIM(val))…

SELECT 
LEFT ('Paul', 3), -- 'Pau'
LEFT ('Paul', 10), -- 'Paul'
RIGHT ('Paul', 3), -- 'ual'
SUBSTRING('Peter', 2,2), --'et'
LTRIM (' Me '), --'Me '
RTRIM (' Me '), -- ' Me'

//Watch out for how LEN works (it's RTRIM'ed first).
LEN ('Me '), -- 2 ...wow!!
LEN (' Me '), -- 3 ....wow!
LEN (N' Me '), -- 6 ...wow!
DATALENGTH(' Me '), -- 4 
DATALENGTH(N,' Me '), -- 8
LOWER ('Me'), --'me'
UPPER ('Me')  --'ME'

-- note that Substring is 1 indexed, unlike C#.

Finally, and most importantly sometimes, REPLACE

Note: Remember that for TEXT/NTEXT Columns you have to convert columns out of being TEXT/NTEXT to be able to use the method, and then convert it back to the original column Type:

UPDATE Blog_Entries
SET Entry =
CAST(REPLACE(CAST(Entry AS NVARCHAR(max)),'FindMe','') AS NTEXT)
WHERE Entry LIKE
'%FindMe%';

String Functions: http://msdn.microsoft.com/en-us/library/ms181984.aspx

LEFT: http://msdn.microsoft.com/en-us/library/ms177601.aspx RIGHT: http://msdn.microsoft.com/en-us/library/ms177532.aspx SUBSTRING: http://msdn.microsoft.com/en-us/library/ms187748.aspx LEN: http://msdn.microsoft.com/en-us/library/ms190329.aspx

LTRIM: http://msdn.microsoft.com/en-us/library/ms177827.aspx RTRIM: http://msdn.microsoft.com/en-us/library/ms178660.aspx

LOWER: http://msdn.microsoft.com/en-us/library/ms174400.aspx UPPER: http://msdn.microsoft.com/en-us/library/ms180055.aspx