Split data from 2 symbols

Question

have table

CREATE TABLE #tbl 
(
   id int identity(1,1),
   obj_type int ,
   obj_id nvarchar(50)
)

have data like : 153:0|114:0|147:0|148:0|152:0|155:0 want insert which data ise before " : " to obj_id , which data is next to " : " insert tu obj_type. it's must be like

id       obj_type     obj_id
1           0           153
2           0           114
3           0           147
4           0           148
5           0           152
6           0           155

How do it in stored procedure ? not function

Curious why not function only stored procedure ? – Suraj Singh Nov 26 '13 at 08:59 — Suraj Singh, Nov 26 '13 at 08:59

score 2 · Accepted Answer · answered Nov 26 '13 at 09:23

declare @S varchar(100) = '153:0|114:0|147:0|148:0|152:0|155:0'

declare @xml xml

select @xml = '<item><value>'+replace(replace(@s, ':','</value><value>'), '|','</value></item><item><value>')+'</value></item>'

select N.value('value[1]', 'int') as obj_id,
       N.value('value[2]', 'int') as obj_type
from @xml.nodes('item') as T(N)

SQL Fiddle

score 0 · Answer 2 · answered Nov 26 '13 at 09:04

You can wait for some experts answer till then you can give it one chance

insert into #tbl 
SELECT LEFT(splitdata, CHARINDEX(':', splitdata) - 1) AS obj_id, 
       RIGHT(splitdata, CHARINDEX(':', REVERSE(splitdata)) - 1) AS obj_type from (select splitdatafrom fnSplitString(parameterName,'|')

now you can write stringsplit function like this

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

thank you for answering, when i try this have an error Invalid column name 'splitdata' , I execute funqcion — GeoVIP, Nov 26 '13 at 09:13
in my answer i have splidata and from together , may be this causing, separate them split data is the column returning by split function, so you can test separately is the function returning 2 rows with splidata column name — M Akela, Nov 26 '13 at 09:24

score 0 · Answer 3 · edited May 23 '17 at 11:49

Another Solution :

Create FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
    )
    RETURNS TABLE
    AS
        RETURN ( SELECT [Value] FROM 
          ( 
            SELECT 
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

taken form: T-SQL split string

And then select the values:

Declare 
@Text varchar (100) = '153:0|114:0|147:0|148:0|152:0|155:0',
@Delim varchar(50) = ':0|'

select case when CHARINDEX(':0', Value) > 0 then Left(Value, Len(Value)-2) else Value   End AS Result  from dbo.SplitString(@Text, @Delim)

score 0 · Answer 4 · edited Nov 26 '13 at 18:00

CREATE procedure   [dbo].[Insert_procedure] 
    @inputString varchar(max)
    AS
    BEGIN   
    set @inputString  ='2153:770|114:0|147:0|148:0|152:0|155:0' Declare @delimiter char(1) = '|' Declare @delimiter_Colon char(1) = ':' 
    DECLARE @chIndex int DECLARE @chIndex1 int DECLARE @item varchar(100)Declare @ReverseString varchar(max)    
    SELECT @ReverseString =   Reverse(substring(reverse(@inputString), 1, 1))
    IF(@ReverseString <> '|')
    set @inputString = @inputString +'|'    
    WHILE CHARINDEX(@delimiter, @inputString, 0) <> 0
        BEGIN            
            SET @chIndex = CHARINDEX(@delimiter, @inputString, 0)            
            SELECT @item = SUBSTRING(@inputString, 1, @chIndex - 1)           
            IF LEN(@item) > 0
                BEGIN
                 set @chIndex1 = CHARINDEX(@delimiter_Colon, @item, 0)
                 Declare @obj_type int Declare @obj_id varchar(50)
                 SELECT @obj_id = SUBSTRING(@item, @chIndex1+1,len(@item)) SELECT @obj_type = SUBSTRING(@item,1,@chIndex1-1)                  
                 Insert into TEST(obj_type,obj_id) values (@obj_type,@obj_id)                                                                             
                END           
            SELECT @inputString = SUBSTRING(@inputString, @chIndex + 1, LEN(@inputString))
        END
   END

Split data from 2 symbols

4 Answers4