how to trim away the first 'x' and last 'y number of characters from a string

Question

Post reply

how to trim away the first 'x' and last 'y number of characters from a string

siri_rmsi

Old Hand

Points: 348
More actions
May 4, 2016 at 7:18 am

#400111

Hi all,
how to trim away the first 'x' and last 'y number of characters from a string
for example:
adventure-works\mary0
adventure-works\jill0
adventure-works\james1
adventure-works\peter0
adventure-works\jo0
adventure-works\guy1
adventure-works\mark1
adventure-works\britta0
adventure-works\margie0
adventure-works\rebecca0
i just need
mary
jill
james
peter
joo
...... in my column
using substring: select substring(LoginID,17,_____) from abc(Here LoginID is the column name)
how to give the third argument??
Thanks,
siri

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Replace 'y' with the number of characters to trim off the end

DECLARE @STR VARCHAR(100) = 'adventure-works\peter0'

select substring(@str,17,LEN(@str)-(17-1) - y)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11642 More actions · Answer 2

Do you care about running that against any other possible db's? You might want something a little more generic.

DECLARE @strings TABLE (LoginID VARCHAR(100))

INSERT INTO @strings

VALUES ('adventure-works\peter0'), ('Northwind\mary0'), ('Test\mark1')

SELECT

SUBSTRING(LoginID,CHARINDEX('\', LoginID) + 1, LEN(LoginID)-CHARINDEX('\', LoginID)-1)

FROM

@strings

This still implies that you only have 1 unwanted character at the end. If you want something that also strips out any amount of numbers than it gets a little trickier but still possible. The less you hard code values the more re-usable your code will be.

Cheers,

SELECT quote FROM brain WHERE original = 1
0 rows returned

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

Another way. And this will work for multiple trailing numbers or when there aren't any.

DECLARE @strings TABLE (LoginID VARCHAR(100))

INSERT INTO @strings

VALUES ('adventure-works\peter0'), ('Northwind\mary009'), ('Test\mark');

SELECT SUBSTRING(p, 1, ISNULL(NULLIF(PATINDEX('%[0-9]%',p),0),8000)-1)

FROM

(

SELECT p = SUBSTRING(LoginID,CHARINDEX('\', LoginID)+1,8000) FROM @strings

) prep;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001