Trim a string

Question

Trim a string

kev43barrie

Right there with Babe

Points: 756
More actions
December 9, 2011 at 9:56 am

#247499

Hello
I have a select column with the format 'Physician->Scotland->Scotland North->NOS431 Core Medical Training' I want to trim everything from the right down to and including the first -> from the right so I would be left with 'Physician->Scotland->Scotland North' .
Everything after Physician-> may be different.
Any ideas?
Kev

Viewing 10 posts - 1 through 9 (of 9 total)

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

okbangas SSChampion Points: 11773 More actions · Answer 1

Use reverse first to get the reverse string, search for the position of >-, use sub string on the reverse string, then reverse it again for your result

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

kev43barrie Right there with Babe Points: 756 More actions · Answer 2

kev43barrie

Right there with Babe

Points: 756

December 9, 2011 at 10:27 am

#1419533

Thanks

R.P.Rozema SSChampion Points: 12317 More actions · Answer 3

Just for fun, here's a query that finds all path components from your strings using a tally table:

select top 11000 identity(int, 1, 1) n

into #Tally

from master.sys.syscolumns sc1

cross join master.sys.syscolumns sc2;

alter table #Tally add

primary key clustered (n);

select tbl.PID, row_number() over (partition by tbl.PID order by t.n) as nr,

substring(tbl.path, t.n + 2, -1 + patindex('%->%', substring(tbl.path, t.n + 2, len(tbl.path))))

from (

select 1 as PID, '->' + 'Physician->Scotland->Scotland North->NOS431 Core Medical Training' + '->' as [PATH]

union all select 2, '->' + 'SQL developer->Nederland->Zuid-Holland->SQL->2012 training' + '->'

) tbl

cross join #Tally t

where t.n < len(tbl.path) - 1

and substring(tbl.path, t.n, 2) = '->'

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Lowell SSC Guru Points: 323518 More actions · Answer 4

a visualization and explanation form a forum post on the same subject:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 5

DECLARE @String VARCHAR(100) = 'Physician->Scotland->Scotland North->NOS431 Core Medical Training';

SELECT LEFT(@String, LEN(@String) - CHARINDEX('>-', REVERSE(@String))-1)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150467 More actions · Answer 6

DATALENGTH is a safer bet than LEN, due to the problem with trailing spaces.

DECLARE @String VARCHAR(100) = 'Physician->Scotland->Scotland North->NOS431 Core Medical Training ';

SELECT LEFT(@String, LEN(@String) - CHARINDEX('>-', REVERSE(@String))-1)

SELECT LEFT(@String, DATALENGTH(@String) - CHARINDEX('>-', REVERSE(@String))-1)

It's probably worth thinking of a more relational design at some point too.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

GSquared SSC Guru Points: 260824 More actions · Answer 7

SQL Kiwi (12/13/2011)
DATALENGTH is a safer bet than LEN, due to the problem with trailing spaces.
DECLARE @String VARCHAR(100) = 'Physician->Scotland->Scotland North->NOS431 Core Medical Training ';
SELECT LEFT(@String, LEN(@String) - CHARINDEX('>-', REVERSE(@String))-1)
SELECT LEFT(@String, DATALENGTH(@String) - CHARINDEX('>-', REVERSE(@String))-1)
It's probably worth thinking of a more relational design at some point too.

But do keep in mind that DataLength on Unicode strings can require math on the CharIndex result, or you'll get the wrong position. Len vs DataLength depends on the constraints of what you're dealing with.

Definitely a good point to bring up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150467 More actions · Answer 8

GSquared (12/14/2011)
But do keep in mind that DataLength on Unicode strings can require math on the CharIndex result, or you'll get the wrong position. Len vs DataLength depends on the constraints of what you're dealing with. Definitely a good point to bring up.

This is true. Where a function has to deal with both, I often include a modification like this:

-- ANSI

DECLARE @String VARCHAR(100) = 'Physician->Scotland->Scotland North->NOS431 Core Medical Training ';

SELECT LEFT(@String, (DATALENGTH(@String) / DATALENGTH(LEFT(@String, 1))) - CHARINDEX('>-', REVERSE(@String))-1)

GO

-- Same formula with Unicode

DECLARE @String NVARCHAR(100) = N'Physician->Scotland->Scotland North->NOS431 Core Medical Training ';

SELECT LEFT(@String, (DATALENGTH(@String) / DATALENGTH(LEFT(@String, 1))) - CHARINDEX('>-', REVERSE(@String))-1)

Still not perfect, since it will produce incorrect results for a string that does not contain '>-', and will fail with an error on a zero-length string 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

GSquared SSC Guru Points: 260824 More actions · Answer 9

SQL Kiwi (12/14/2011)
GSquared (12/14/2011)
But do keep in mind that DataLength on Unicode strings can require math on the CharIndex result, or you'll get the wrong position. Len vs DataLength depends on the constraints of what you're dealing with. Definitely a good point to bring up.
This is true. Where a function has to deal with both, I often include a modification like this:
-- ANSI
DECLARE @String VARCHAR(100) = 'Physician->Scotland->Scotland North->NOS431 Core Medical Training ';
SELECT LEFT(@String, (DATALENGTH(@String) / DATALENGTH(LEFT(@String, 1))) - CHARINDEX('>-', REVERSE(@String))-1)
GO
-- Same formula with Unicode
DECLARE @String NVARCHAR(100) = N'Physician->Scotland->Scotland North->NOS431 Core Medical Training ';
SELECT LEFT(@String, (DATALENGTH(@String) / DATALENGTH(LEFT(@String, 1))) - CHARINDEX('>-', REVERSE(@String))-1)
Still not perfect, since it will produce incorrect results for a string that does not contain '>-', and will fail with an error on a zero-length string 🙂

Yep.

Real solutions to this involve crazy things like actually normalizing data to the point where you store a hierarchy as rows, instead of concatenating it into a string. And, of course, by "crazy", I mean "correct". 🙂

With the hierarchy normalized, operations like this become trivial.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon