CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2005
ZigiZ
ZigiZ

Reputation: 2520

How to LTRIM/RTRIM specific chars?

I have this nvarchar string for example: 

declare @s as nvarchar(max)
set @s = '  ##a# bc##   ###'

So I need the spaces to be trimed but also the # chars.
How can I return just 'a# bc'?

(number of leading and trailing # may vary)

Upvotes: 2

Views: 507

Answers (2)

MicSim
MicSim

Reputation: 26806

You could use the following generic functions to cut arbitrary leading and trailing chars, by simply calling

dbo.LTrimX(dbo.RTrimX(' ##a# bc### ', ' #'), ' #')

Here the UDFs (adapted from here):

CREATE FUNCTION dbo.LTrimX(@str VARCHAR(MAX), @trimchars VARCHAR(255)) RETURNS VARCHAR(MAX)
     AS
     BEGIN  
     IF @str LIKE '[' + @trimchars + ']%'
     SET @str =STUFF ( @str , 1 , PATINDEX('%[^' + @trimchars + ']%', @str)-1 , '')
     RETURN @str
END

CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX), @trimchars VARCHAR(255)) RETURNS VARCHAR(MAX)
     AS
     BEGIN
     IF @str LIKE '%[' + @trimchars + ']' 
     SET @str = REVERSE(dbo.LTrimX(REVERSE(@str), @trimchars))
     RETURN @str
END
GO

Upvotes: 4

Dibstar
Dibstar

Reputation: 2364

Assuming that the abc part is only ever made up of numbers and letters:

DECLARE @s NVARCHAR(50)
SET @s = ' ##a# bc### '

SELECT 
SUBSTRING(@s,PATINDEX('%[A-Za-z0-9]%',@s),LEN(@s) - PATINDEX('%[A-Za-z0-9]%',REVERSE(@s)) -1) as string2

Upvotes: 4

Related Questions