15,741,818 members
0.00/5 (No votes)
See more:
Hello,
I would apprecite help in order to solve my problem...
I have table in SQL Server db representing hierarchy...
Also, there is column which contains '#' singn as delimiter for the level...
Here is how column looks like:

```All Values
#Loans
##Revolving
###Credit Cards```

I would like to make function that will transform this column in 4 separated colums (using '#' as delimiter) with the result as follows:
```Column1       Column2       Column3     Column4
All Values
Loans
Revolving
Credit Cards```

Can someone please help me with this?
Thanks in advance for your time and effort.

Regards,
Posted
Updated 14-Feb-12 0:36am
v2
Comments
manognya kota 14-Feb-12 5:31am
Sorry,Could not understand the question.
Manfred Rudolf Bihy 14-Feb-12 6:37am
After adding the pre tags the indentation that was already added by OP shows what his intentions are.
RDBurmon 14-Feb-12 6:39am
No Manognya , You could . It is very straight question explained in simple language
Let the op know what you have not understand.
manognya kota 14-Feb-12 7:01am
Hi Rahul,
Could not understand it before the indentation.Now it fine.
Thanks.

## Solution 4

SQL
```DECLARE @Table1 AS TABLE(rwid bigint ,val  NVARCHAR(MAX))
DECLARE @coun as bigint
DECLARE @x as bigint
DECLARE @Table2 AS TABLE(rwid bigint identity(1,1),column1 NVARCHAR(255),column2 NVARCHAR(255),column3 NVARCHAR(255),column4 NVARCHAR(255))
DECLARE @col1 AS NVARCHAR(255)
DECLARE @col2 AS NVARCHAR(255)
DECLARE @col3 AS NVARCHAR(255)
DECLARE @Col4 AS NVARCHAR(255)
DECLARE @nextlevelQuilifier as bigint
DECLARE @Status AS NVARCHAR(50)

SET @x=1
SET @nextlevelQuilifier=1

INSERT INTO @Table1
SELECT 1,'All Values'
Union
SELECT 2,'#Loans'
Union
SELECT 3,'##Revolving'
Union
SELECT 4,'###Credit Cards'

SELECT @coun=COUNT(*) FROM @Table1

While @coun >= @x
Begin
SELECT @nextlevelQuilifier=[dbo].[ufn_CountChar](val,'#') FROM @Table1 WHERE rwid=@x

IF  @nextlevelQuilifier=0
BEGIN
SELECT @col1= REPLACE(Val,'#','') FROM  @Table1 WHERE rwid=@x
INSERT INTO @Table2(column1,column2,column3,column4)
SELECT @col1 ,'','' ,''
END

ELSE IF  @nextlevelQuilifier=1
BEGIN
SELECT @col2= REPLACE(Val,'#','') FROM  @Table1 WHERE rwid=@x
INSERT INTO @Table2 (column1,column2,column3,column4)
SELECT '' ,@col2 ,'' ,''
END

ELSE IF  @nextlevelQuilifier=2
BEGIN
SELECT @col3= REPLACE(Val,'#','') FROM  @Table1 WHERE rwid=@x
INSERT INTO @Table2 (column1,column2,column3,column4)
SELECT '','',@col3 ,''
END

ELSE IF  @nextlevelQuilifier=3
BEGIN
SELECT @col4= REPLACE(Val,'#','') FROM  @Table1 WHERE rwid=@x
INSERT INTO @Table2 (column1,column2,column3,column4)
SELECT '','','',@Col4
END

set @x=@x+1

End

SELECT * from @Table2 order by rwid```

For this you need below function for counting char in string

SQL
```CREATE FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar NVARCHAR(255) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = LEN(@pInput)

WHILE @vIndex <= @vInputLength
BEGIN
IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar
SET @vCount = @vCount + 1

SET @vIndex = @vIndex + 1
END

RETURN @vCount

END
GO```

Hope this helps if yes then please accept and vote the answer otherwise revert back with your queries
--Rahul D.

Comments
RDBurmon 14-Feb-12 8:24am
Thanks Please vote.
RedDk 14-Feb-13 15:38pm
Thanks RD,
More than a few insights into TSQL from your im(h)o "unique" style

## Solution 2

I know this is not what you asked for, but since you labled your question with SQL2008 have you given Hierarchy ID[^] any thought yet? The article explains how to model a hierarchy using standard T-SQL means and then goes on to demonstrate how to implement the same using a hierarchy id.

Regards,

Manfred

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 65 Richard MacCutchan 60 Gerry Schmitz 25 Rick York 20 Valery Possoz 10
 OriginalGriff 1,568 Richard MacCutchan 1,318 merano99 480 Rick York 430 Dave Kreskowiak 276

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900