SUBSTRING

-- Truncate two last characters
IF LEN(@MyString)>2 SET @MyString = SUBSTRING(@MyString, 1, LEN(@MyString)-2)

CHARINDEX

DECLARE @SQL VARCHAR(200)
SET @SQL = 'SELECT * FROM Customer WHERE ID=8'
IF CHARINDEX(' where ', LOWER(@SQL))>0 
    PRINT 'Where clause exists'

Get a substring on the right from the last '/':

DECLARE @val VARCHAR(250)
SET @val = '/Folder1/Folder2/file.txt'
SET @val = RIGHT(@val, CHARINDEX('/', REVERSE(@val)) - 1)
PRINT @val -- file.txt

Get a substring on the left up to the last '(':

DECLARE @val VARCHAR(250)
SET @val = 'abc(1).pdf'
SET @val = LEFT(@val, LEN(@val) - CHARINDEX('(', REVERSE(@val)))
PRINT @val -- abc

Get characters on the left from the first space:

CREATE FUNCTION fn_GetLeftFromSpace(@str AS VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @val VARCHAR(255)
    IF CHARINDEX(' ',@str) != 0
        SET @val = SUBSTRING(@str, 1, CHARINDEX(' ', @str))
    ELSE
        SET @val = @str
    RETURN @val
END
GO
 
SELECT dbo.fn_GetLeftFromSpace('one two three') -- result: one
SELECT dbo.fn_GetLeftFromSpace('one_two three') -- result: one_two

Get an extension of a filename:

SELECT SUBSTRING([Filename], LEN([Filename])-(CHARINDEX('.', REVERSE([Filename]))-2), 5000)

Replace a substring on the right from a specified character:

IF OBJECT_ID('fn_ReplaceOnRightFromChar') IS NOT NULL DROP FUNCTION fn_ReplaceOnRightFromChar
GO
 
CREATE FUNCTION fn_ReplaceOnRightFromChar(@ch AS CHAR(1), @str AS VARCHAR(2000), @REPLACE AS VARCHAR(1000))
RETURNS VARCHAR(2000)
AS
BEGIN
    DECLARE @val VARCHAR(2000), @LastIndex INT
    SET @LastIndex = LEN(@str) - CHARINDEX(@ch, REVERSE(@str)) + 1
 
    IF @LastIndex != 0 AND @REPLACE IS NOT NULL
        SET @val = SUBSTRING(@str, 1, @LastIndex) + @REPLACE
    ELSE
        SET @val = @str
 
    RETURN @val
END
GO
 
SELECT dbo.fn_ReplaceOnRightFromChar('X', 'This will stay X This will be replaced', 'New content')
-- Result: This will stay XNew content

Combine parts of a URL:

DECLARE @url VARCHAR(50), @host VARCHAR(50), @path VARCHAR(50)
SET @host = 'http://www.test.com'
SET @path = 'docs/index.htm'
 
SET @url = @host
IF LEN(@host)>0
BEGIN
    -- Test if @host ends with '/'. If not, add it.
    IF SUBSTRING(@host, LEN(@host), 1)<>'/' SET @host = @host + '/'
END
-- Test if @path starts with '/'. If so, remove it.
IF SUBSTRING(@path, 1, 1) = '/' SET @path = SUBSTRING(@path, 2, LEN(@path)-1)
 
-- Combine host and path.
SET @url = @host + @path
 
PRINT @url -- Results: http://www.test.com/docs/index.htm

Remove characters at the end of the string:

-- Note: SQL Server removes trailing spaces automatically i.e. 'aaa' is the same as 'aaa ' and 'aaa    '.
DECLARE @recipe VARCHAR(500), @N INT
SET @N = 3
SET @recipe = 'noodles.+.vegetables.+.tofu.+.'
 
-- The last three characters are '.+.'
IF LEN(@recipe) >= @N SET @recipe = SUBSTRING(@recipe, 1, LEN(@recipe)-@N)
 
PRINT @recipe -- Results: noodles.+.vegetables.+.tofu

Get the hexadecimal code of a character:

IF OBJECT_ID('dbo.fun_GetHexCode') IS NOT NULL DROP FUNCTION dbo.fun_GetHexCode
GO
CREATE FUNCTION dbo.fun_GetHexCode(@c AS CHAR(1)) 
RETURNS CHAR(2) 
AS
BEGIN
    DECLARE @hex CHAR(16)
    SET @hex='0123456789ABCDEF' 
    RETURN SUBSTRING(@hex,(ASCII(@c)/16)+1,1) + SUBSTRING(@hex,(ASCII(@c)&15)+1,1)
END 
GO
 
PRINT dbo.fun_GetHexCode(' ') -- 20
PRINT dbo.fun_GetHexCode('A') -- 41
PRINT dbo.fun_GetHexCode('+') -- 2B
PRINT dbo.fun_GetHexCode('z') -- 7A

Get the first four words from a string. Assume spaces are word separators:

IF OBJECT_ID('dbo.fun_GetSubject') IS NOT NULL DROP FUNCTION dbo.fun_GetSubject
GO
CREATE FUNCTION dbo.fun_GetSubject(@str AS VARCHAR(2000))
RETURNS VARCHAR(2000)
AS
BEGIN
    DECLARE @i INT, @pos INT, @pos_new INT, @ret VARCHAR(2000)
    SET @i=0
    SET @pos=1
    SET @pos_new=1
 
    WHILE @pos_new>0 AND @pos_new<=21 AND @i<4
    BEGIN
        SET @pos_new=CHARINDEX(' ',@str,@pos_new)
        IF @pos_new>0 AND @pos_new<=21 AND @i<4
        BEGIN
            SET @pos=@pos_new
            SET @pos_new=@pos_new+1 
            SET @i=@i+1
        END
        IF @pos_new>21 AND @i=0 SET @pos=@pos_new
        IF @pos_new<=0 AND @i=0 SET @pos=LEN(@str)
    END
    SELECT @ret=RTRIM(LTRIM(SUBSTRING(@str,1,@pos)))
    IF LEN(@str)>LEN(@ret) SET @ret=@ret+' ...'
 
    RETURN @ret
END
GO
 
PRINT dbo.fun_GetSubject('This is a very long sentence that eventually ends.')
-- Results: This is a very ...