Split field into different columns

[munchen]

I have a field called reasons that displays the various reasons separated by an underscore character like so:

Reasons
NoDate_Late_NoSignature

What I need to do is split the reasons like so:
Reason1 Reason2 Reason3
NoDate Late NoSignature

How can I achieve this? In the reasons column at present there may be anything from 1 to 9 reasons (separated by an underscore). The reason column comes from a database field but i want to split this field like i have shown.

 

[Katy44]

There will be a way to do it, but the easiest way in my opinion is to get the PK field and this field into excel, delimit the field that has reasons separated by an underscore, change the table structure, build up an update string for each one in excel and then run the created strings.
May not be easiest for you, but it's just how I would go about it.

 

[munchen]

Katy44

Thanks for your idea Katy but I cannit change the table structure so I need some sort of loop to count the number of reasons and then put each reason into its own column eg Reason1,Reason2,Reason3,Reason4 etc.

 

[Catadmin]

You should be able to write a sproc or query that pulls out the values based on where they are in the string.

Someone at one of my old workplaces wrote a UDF that would read in a string and a delimeter character as input, then count the # of delimeters in the string. I don't have that code here, but I'll try to rewrite it in a bit.

Based on this UDF, I came up with a little piece of code myself that pulled out the various values in the string based on the position of delimeters as determined by the UDF. The difference is, I had a set of fields that were separated by a , and then the entire record set was separated from the next record set by a | :

create procedure up_MPDep(@Array varchar(8000), @PartID int)
AS
Declare @DepFName nvarchar(100), @DepLName nvarchar(150), @DepMInitial nvarchar(1) 
	
Set @DepDelimCount = MultiProduct.dbo.ArrayLength(@Array, '|')
Set @DepFName = ''
Set @DepLName = ''
Set @DepMInitial = ''


-- This procedure was created by Brandie Tarvin on 4/8/05 for the MP Program
-- It adds a potentially unlimited # of Dependents to the Dep table based
-- on an array and participant # inputed.  Each set of dep records are delimited by a '|'
-- and each field is determined by the User Defined Function ElementAtPosition in the MP DB.
-- The array starts with an index of Zero.

While @DepDelimCount <> 0
Begin
    Set @DepFName = Multiproduct.dbo.ElementAtPosition(@Array,',',0)
    Set @DepMInitial = Multiproduct.dbo.ElementAtPosition(@Array,',',1)
    Set @DepLName = Multiproduct.dbo.ElementAtPosition(@Array,',',2)
    
  
  Insert Into Dep(FirstName, LastName, MiddleInitial)
  Values(@DepFName, @DepLName, @DepMInitial)    		
  
  Set @DepDelimCount = @DepDelimCount - 1

END

This probably doesn't help you directly, but it might give you ideas on where to go next. The UDF involved a SubString() function and two counters to determine where the delimeter was, that much I remember. I believe there was also either a cursor or a While loop to cycle through the entire string one character at a time.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Katy44]

If the table structure is already prepared then my suggestion will work - just create the update statement.

 

[munchen]

I thought i'd provide more of an example to explain things better.

The field I am using is called Reasons and there can be 1 to about 10 reasons like so:

ID Reason
1 aaa
2 aaa_bbb
3 aaa_bbb_ccc_ddd
4 aaa
5 aaa_ccc_ddd_eee_fff

As you can see each reason can have numerous reasons so I need to loop through this field checking the number of underscore characters.

Each reason is separated by an underscore character.As i said for each id I need to split each reason like so:

ID Reason1 Reason2 Reason3 Reason4 Reason5
1 aaa
2 aaa bbb
3 aaa bbb ccc ddd
4 aaa
5 aaa ccc ddd eee fff

I know this can be done so any ideas would be greatly appreciated.

 

[munchen]

Please help with this one as its turning into a nightmare.

I assume I'll have to do a loop when searching the Reason field for the underscore character and when I find an occurence I'll have to save the characters to the left of the underscore as Reason1.
Then begin the loop again from the underscore i'd just found and search for another occurrence and then use the characters to the left of this underscore but before the previous underscore(Reason1) and save this as Reason2 and so on until there are no more underscore characters.

Can someone please give me a pointer on this?

 

[Katy44]

I don't understand, are these reason1, reason2...reason5 fields already set up?
Or are you going to have a link table which would be a much better solution?
If the fields I mention above aren't already created and you can't change the table structure, what do you intend to do?

 

[maswien]

Assume your table name is contract, following script add two more tables contract_reason and reasons, I think it's a better database design than adding more columns in this case.

create table contract ( contract_id int, contract_name varchar(50), reason varchar(1000))

you want ignore above because you have your own table, the important thing is it has a unique key contract_id or whatever.

select contract_id, reason into contract_reason from contract

while (@@rowcount > 0 )
begin
 insert into contract_reason 
 select contract_id, left(reason, charindex('_', reason) - 1 ) from contract_reason
 where  charindex('_', reason) > 0

 update contract_reason set reason = right(reason, len(reason) - charindex('_', reason) )
 where charindex('_', reason) > 0
end

select distinct reason into reasons from contract_reason

alter table reasons add reason_id int identity(1,1)

alter table contract_reason add reason_id int

update contract_reason set reason_id = t1.reason_id
from contract_reason t0 inner join reasons t1
 on t0.reason = t1.reason

alter table contract_reason drop column reason


Now you can get reasons for a particular contract using following query:

select reason
from contract c inner join contract_reason cr on c.contract_id=cr.contract_id
     inner join reasons r on cr.reason_id = r.reason_id

 

[munchen]

Katy44

Basically what I am trying to do (badly) is I have a field called Reasons that can have 1 or more reasons separated by an underscore. I then want to run an sproc that will split this field into however number of reasons there is.

EG
Reasons
aaa
bbb_ccc
vvv_ggg_ppp

Would become:
Reason1 Reason2 Reason3
aaa ccc ppp
bbb ggg
vvv

Hope this makes more sense. I don't want to add more columns to the table just split the field in an sproc.

 

[Catadmin]

Lordy, lordy. It took me long enough to find my code.

Here are two UDFs that might help you out. They were created for a VB program which inserts a delimited string of several record sets, but you should be able to use them to pull apart your current column.

First UDF:

-- This UDF Counts the # of recordsets in a large 
-- string by counting the delimit character and returns 
-- that count for parsing with the ElementAtPosition UDF

CREATE FUNCTION ArrayLength (@Array varchar(8000), @Delimeter varchar)  
RETURNS int AS  
BEGIN 
DECLARE @Count int, @Counter int
SET @Count = 0
SET @Counter = 0

IF SUBSTRING(@Array, @Counter, 1) <> @Delimeter
BEGIN
SET @Count = @Count + 1
END

WHILE @Counter < LEN(@Array)
BEGIN
IF SUBSTRING(@Array, @Counter, 1) = @Delimeter AND
LEN(@Array) - @Counter > 0
BEGIN
SET @Count = @Count + 1
END
SET @Counter = @Counter + 1
END

RETURN @Count
END

Here's the second one:

CREATE FUNCTION ElementAtPosition (@Array varchar(8000), @Delimeter
varchar, @Position int)  
RETURNS varchar(100) AS  
BEGIN 
DECLARE @Count int, @Counter int, @ElementAtPosition varchar(100),
@LastDelimeter int
SET @Count = 0
SET @Counter = 0
SET @ElementAtPosition = ''
SET @LastDelimeter = -1
SET @Array = @Array + @Delimeter + @Delimeter

WHILE @Counter < LEN(@Array)
BEGIN
IF SUBSTRING(@Array, @Counter, 1) = @Delimeter
BEGIN
IF @Count = @Position
BEGIN
SET @ElementAtPosition =
SUBSTRING(@Array, @LastDelimeter + 1, @Counter - @LastDelimeter -1) 
END

SET @Count = @Count + 1
SET @LastDelimeter = @Counter

END
SET @Counter = @Counter + 1
END

IF @ElementAtPosition = ''
SET @ElementAtPosition = '' --'Empty Element'

RETURN @ElementAtPosition
END

If you look at my last post above, I only used the first UDF to count the # of delimiters so that I could parse into various variables different elements. Combine my two posts and they should help, without you needing to add additional columns. You can simply make this a stored procedure that you call and parse at will.

Hope this helps!



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[munchen]

Catadmin

I'm quite new to sql so bear with me. When you say "You can simply make this a stored procedure that you call and parse at will" do i just copy the code into an sp and alter it for my fields etc?