T_SQL to exctact data between Special Chars

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

T_SQL to exctact data between Special Chars

Author

Topic

Danny_SQL_9876
Starting Member

8 Posts

Posted - 2012-02-17 : 15:35:23

Hi All,

I have a table with two columns ID, Path (following is the sample of the table). please excuse the formatting.

ID Path
11------------ .16
13------------ .11.13
4------------- .10.4
6------------- .1.3.6
14------------ .7.899
9------------- .10.3.4.600.7
34------------ .76.1

And am trying to build a t-sql query which will return results as following

ID --Path ---------Level 1 ---Level 2 ---Level 3 ---Level 4 ---Level 5
11 --.16 --------16
13 --.11.13 --------11 --------13
4 --.10.4 ----------10 --------4
6 --.1.3.6 ----------1 --------3 ----------6
14 --.7.899 ---------7 --------899
9 --.10.3.4.600.7 ---10 -------3 ----------4 --------600 --------7
34 --.76.1 ----------76 -------1

Notice path always start with a dot and ends without a dot! And the max path level will be 5.

Tried using SUBSTRING and CHARINDEX but was not able to get desired results. Any help is appreciated. Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 15:56:08

you need to use string parsing function like below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Danny_SQL_9876
Starting Member

8 Posts

Posted - 2012-02-17 : 17:32:29

Hi All,

I finally managed to get the code .Thank you so much for all your help
following query takes the string and splits it in the same row.

--Start--

CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT TOP (@n) n AS Number FROM Nums WHERE n <= @n ORDER BY n;
GO
-- Erland's split function
CREATE FUNCTION dbo.inline_split_me(@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(
SELECT
ROW_NUMBER() OVER(ORDER BY Number) AS pos,
ltrim(
rtrim(
convert(nvarchar(4000), substring(@param, Number,
charindex(N',' COLLATE Slovenian_BIN2, @param + convert(nvarchar(MAX), N','), Number) - Number)
))) AS Value
FROM dbo.fn_nums(convert(int, len(@param)))
WHERE substring(convert(nvarchar(MAX), N',') + @param, Number, 1) = N',' COLLATE Slovenian_BIN2
)
GO
DECLARE @T TABLE (
id int NOT NULL IDENTITY(1, 1) UNIQUE,
data varchar(max)
);

insert @T values('DEF,KHL,MNO');
insert @T values('DEF,KHL,MNO,LKJ,MNJ,BKS');

SELECT
*
FROM
@T AS T
OUTER APPLY
dbo.inline_split_me(T.data) AS S
PIVOT
(
MAX(Value)
FOR pos IN ([1], [2], [3], [4], [5], [6])
) AS P;
GO
DROP FUNCTION dbo.inline_split_me, dbo.fn_nums;
GO

--- END----

Original source - http://social.msdn.microsoft.com/Forums/en/transactsql/thread/b01291e9-03f7-457c-bcd2-f1241b0aac3f

Replacing the

insert @T values('DEF,KHL,MNO');
insert @T values('DEF,KHL,MNO,LKJ,MNJ,BKS');

with my select statement containing the field i wanted to parse did the magic.hope this helps someone with similar issue. cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources