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 ...