Trim a string
-
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
-
Thanks
-
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) = '->'
-
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! -
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
-
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 -
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
-
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 -
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply