SQL Server Performance

sql stored procedure - parse string help

Discussion in 'ALL SQL SERVER QUESTIONS' started by abk, Mar 4, 2012.

  1. abk New Member

    I have a string to be parsed using stored procedure.

    'unit=935457 unittype=5 dim=3 kind=777 address=1201.12.3434.12 name=hello'

    I want to get an output string as follows

    93457 5 3 777 1201.12.3434.12 hello

    How can I get this output using a sql server stored procedure ?

    Thanks
  2. Shehap MVP, MCTS, MCITP SQL Server

    First , welcome to Forums…

    You could work out the below SP

    CREATEPROCEDURE GETRESULTS

    @unit varchar (10)
    , @unittype varchar (10)
    , @dim varchar (10)
    , @kind varchar (10)
    , @address varchar (100)
    , @name varchar (10)

    AS
    BEGIN

    SELECT @unit +' '+@unittype+' '+@DIM+' '+@kind+' '+@name

    END

    EXEC GETRESULTS @unit =935457 ,@unittype =5,@dim =3 , @kind =777 ,@address ='1201.12.3434.12',@name ='hello'
  3. abk New Member

    Hello:

    Thanks. I wanted a more general procedure which will work with any name=value pair. I want the values only in a string.

    Thanks

  4. FrankKalis Moderator

    Welcome to the forum!
    T-SQL is not the language of choice for string parsing. You're far better off doing this in some client langugage with better support. Maybe something like this will get you some ideas, but I would really look for a solution outside T-SQL.

    Code:
    IF OBJECT_ID('dbo.RemoveChars') IS NOT NULL
        DROP FUNCTION dbo.RemoveChars;
    GO
    CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
    RETURNS VARCHAR(1000)
    BEGIN
    DECLARE @pos int;
    
    SELECT @Pos = PATINDEX('%[^0-9]%', @Input);
    
    WHILE @Pos > 0
    BEGIN
        SELECT @Input = STUFF(@Input, @pos, 1,'')
        SELECT @Pos = PATINDEX('%[^0-9]%', @Input)
    END
    
    RETURN @Input;
    
    END
    GO
    
    DECLARE @s varchar(MAX);
    SELECT @s = 'unit=935457 unittype=5 dim=3 kind=777 address=1201.12.3434.12 name=hello';
    
    SELECT
        dbo.RemoveChars(RIGHT(LEFT(@s, Number - 1), CHARINDEX(' ',REVERSE(LEFT(' ' + @s, Number - 1))))),
    
        SUBSTRING(@s, CHARINDEX('name=', @s) + 5, CHARINDEX('=', REVERSE(@s)))
    FROM
        dbo.Number N
    WHERE
        Number BETWEEN 1 AND LEN(@s) AND
        SUBSTRING(@s, Number, 1) = ' ' AND SUBSTRING(@s, Number - 1, 1) <> ' ';
    
  5. abk New Member

    Thanks a lot. I meant to reply to this, but did not figure , how it became another thread. I need to map these to column names of two tables.

    Thanks again for the help.

Share This Page