Separate the string value into different columns

[cestonina]

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

 

[rjohn2001]

try this

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

 

[rjohn2001]

hmm typo error

replace the @String with @List

hope this should help.

 

[cestonina]

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

 

[rjohn2001]

You can call this function like this

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

Select Value from Split(@List,',')

 

[bborissov]

To your needs you must make a simple modification of this function:

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:

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.

 

[cestonina]

Hi all,

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

Cheers,
Chris