0

How to parse unknown length string into different columns by using delimiter ('.').

declare osversion varchar(100)
set osversion = '6.2.9295'
SELECT [Part1] = LEFT(osversion,CHARINDEX('.',osversion) - 1), 
   [Part2] = SUBSTRING(osversion,CHARINDEX('.',osversion) + 1, 
                       CHARINDEX('.',osversion,CHARINDEX('.',
                       osversion) + 1) - (CHARINDEX('.',osversion) + 1)), 
   [Part3] = SUBSTRING(osversion,CHARINDEX('.',
                       osversion,CHARINDEX('.',osversion) + 1) + 1,
                       DATALENGTH(osversion) - CHARINDEX('.',
                       osversion,CHARINDEX('.',osversion) + 1) - 
                       CHARINDEX('.',REVERSE(osversion))), 
       from table1

Result:

Part1   Part2   Part3   
6   2   9295

This result is for fixed length of string. I want to parse for an unknown length of string. Like '86.52.929.695.22.1234'. Please help.

Reporter
  • 3,897
  • 5
  • 33
  • 47
user2454617
  • 1
  • 1

1 Answers1

0

I purpose you to recreate the C#'s string.Split function in SQL. Here is the code 

CREATE FUNCTION [dbo].[f_Split](
@String NVARCHAR (4000), 
@Delimiter NVARCHAR (10)
)

RETURNS @T TABLE ([Value] NVARCHAR(4000))
BEGIN
    DECLARE @NEXTSTRING NVARCHAR(4000)
    DECLARE @POS INT,@DELIM_SIZE INT
    DECLARE @NEXTPOS INT
SELECT
    @NEXTSTRING = '',
    @String = @String + @Delimiter,
    @DELIM_SIZE = LEN(@Delimiter)

SET @POS = CHARINDEX(@Delimiter,@String)
SET @NEXTPOS = 1

WHILE (@POS <>  0)
BEGIN
    SET @NEXTSTRING = SUBSTRING(@String,1,@POS - 1)
    INSERT INTO @T ( [VALUE]) VALUES (@NEXTSTRING)
    SET @String = SUBSTRING(@String,@POS +@DELIM_SIZE,LEN(@String))
    SET @NEXTPOS = @POS
    SET @POS  = CHARINDEX(@Delimiter,@String)
END

 RETURN

END

You can call the function like that 

DECLARE @temp nvarchar(255); SELECT @temp = '86.52.929.695.22.1234'; 
SELECT * FROM dbo.f_Split(@temp,'.');

Here is the output : 

Value
86
52
929
695
22
1234
PascalT
  • 31
  • 2
  • Thank you for the quick reply. But i need the output to be divided into columns not in 1 row. Like Value1 value2 value3 value4 value5 value6. – user2454617 Jun 05 '13 at 08:50
  • Sorry not in 1 row its one column. So the output should be in different columns. – user2454617 Jun 05 '13 at 08:58
  • Ok, I understand what you want to do : to have the rows in column. For this, you should follow this link [sql-server-pivot-dynamic-columns-no-aggregation](http://stackoverflow.com/questions/11985796/sql-server-pivot-dynamic-columns-no-aggregation) – PascalT Jun 05 '13 at 09:11