July 10, 2013 at 4:23 pm
Hi All,
I have a string like this
Building Management - Ex. Existing Facility\Keys & locks\Project Co to ensure that 400 complete sets of keys are available at all times.
now my requirement is to extract string before first \ ,second \ and third \ . So my results would be like this
Building Management - Ex. Existing Facility
Keys & locks
Project Co to ensure that 400 complete sets of keys are available at all times.
what is best way of achieving this in sql server 2008.
July 10, 2013 at 4:54 pm
The best way that comes to my mind is to use the 8K Splitter. Read the following article and come back if you need help.
July 11, 2013 at 2:32 am
As long as you don't have more then four fields, you can use parsename, although this function is specifically intended to parse object names in SQL Server and only accepts '.' as the delimiter. But first place the dots with some other character and then the backslashes with dots:
DECLARE @STR nvarchar(4000) = 'Building Management - Ex. Existing Facility\Keys & locks\Project Co to ensure that 400 complete sets of keys are available at all times.'
; WITH replacements AS (
SELECT replace(replace(@str, '.', char(31)), '\', '.') AS str
)
SELECT replace(parsename(str, 3), char(31), '.'),
replace(parsename(str, 2), char(31), '.'),
replace(parsename(str, 1), char(31), '.')
FROM replacements
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply