Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Separate the string value into different columns

Status
Not open for further replies.

cestonina

Programmer
May 25, 2003
30
SG
Hi there,

I'm quite new in MSSQL. I'm having problems creating my
custom query in MSSQL syntax.

Background: I need to separate the string value of one field into separate columns. See example below.

OriginalField
TRUE,FALSE,Victoria,Phone,"Application Today",...

I need to create a query (select statement) to separate these values into different columns.

Field1 Field2 Field3 Field4
TRUE FALSE Victoria "Application Today"

My problem: there are about 30 comma-delimited values in the original field. Its hard to manually hard code as the length of each comma-delimited value is not fixed.

I've been playing around with the charindex and substring function to create this query. But I observed that charindex doesn't have the flexibility of the instr function of Oracle which makes it difficult to separate each values.

In Oracle the instr function has a optional third argument wherein you can specify the nth occurence of the comma.

Appreciate any help. Thanks in advance.

Regards,
Chris
 
try this

Code:
CREATE FUNCTION dbo.Split2
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
) 
RETURNS @RtnValue table 
(

Id int identity(1,1),
Value nvarchar(100)
) 
AS 
BEGIN

if @String is not null
begin

While (Charindex(@SplitOn,@List)>0)
Begin 

Insert Into @RtnValue (value)
Select 
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 

Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End 

Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))

end
Return
END
 
hmm typo error

replace the @String with @List

hope this should help.
 
Hi rjohn

Thanks for your prompt reply. I already replaced the @String with @List.

Just one last question, can you provide a sample how to use this? :)

I'm still kinda new with UDF in MSSQL. Can you relate it with the example I mentioned above. A basic query showing how to use this syntax would do. Same as with the example below:

eg. select substring(OriginalField, 1, 4) from table --this would give result TRUE.

Thanks again. Appreciate your help.

Regards,
Chris





 
You can call this function like this

@List - this is the string to parse
@SplitOn - this is a Delimiter in this case it is ','

Code:
Select Value from Split(@List,',')
 
To your needs you must make a simple modification of this function:
Code:
CREATE FUNCTION dbo.Split2
(
@List nvarchar(2000) = '',
@SplitOn nvarchar(5) = ',',
@Occurance nvarchar(5) = 1
)
RETURNS @RtnValue varchar(200)
AS
BEGIN
   declare @iOcc int
   SET @iOcc = 0
   While (Charindex(@SplitOn,@List)>0) AND
         @iOcc <> @Occurance
         Begin
           SET @iOcc = @iOcc + 1
           SET @RtnValue = SUBSTRING(@List,1,
                                Charindex(@SplitOn,@List)-1)
           SET @List = SUBSTRING(@List,
                              Charindex(@SplitOn,@List)+1,
                              2000)

         End

   Return
END
Then you could use it:
Code:
INSERT INTO MyTable (Field1, Field2, Field3)
SELECT dbo.Split2(FieldWithLongStr,',',1),
       dbo.Split2(FieldWithLongStr,',',2),
       dbo.Split2(FieldWithLongStr,',',3)
FROM TableWithLongString

(not tested at all)


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
 
Hi all,

Thank you all for your suggestions. I have already tested them and it works! :)

Cheers,
Chris

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top