Taking parsed string and assigning variables without using a temp table

  • Is there a quick way to take in a string of data that has been parsed and assign the parsed strings to variables, without first using a temp table? For example:

    'Doe*Jane*12231968'

    <Some parsing logic>

    Desired parameter assignments:

    @Fname,@Lname,@DOB

    DemicoQ

     

  • Actually, there is!  It comes in the form of the PARSENAME command which was specifically developed to break apart the parts SQL object names "at the dots".  Since your example string uses "*" as the "field seperator", all we have to do is REPLACE the "*" with "." and then use PARSENAME.  An example follows:

    DECLARE @SomeString VARCHAR(100)

    SET @SomeString = 'Doe*Jane*12231968'

    SELECT

    PARSENAME(REPLACE(@SomeString,'*','.'),4) AS PART4,

    PARSENAME(REPLACE(@SomeString,'*','.'),3) AS PART3,

    PARSENAME(REPLACE(@SomeString,'*','.'),2) AS PART2,

    PARSENAME(REPLACE(@SomeString,'*','.'),1) AS PART1

    Part4 Part3 Part2 Part1

    ----- ----- ----- --------

    NULL  Doe   Jane  12231968

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • http://www.sommarskog.se/arrays-in-sql.html might give some ideas.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I use the following procedure, uspGetToken, to return the first substring. The original source string is modified by stripping the first token and the first delimiter string (which can be of any length).

    The modified source string and the token are returned as OUTPUT parameters.

    EXEC this stored procedure for each token (substring) that you expect.

    ----------------------------------------------------------------------

    DROP PROCEDURE uspGetToken

    GO

    CREATE PROCEDURE uspGetToken

    (

      @parm varchar(1000) OUTPUT,

      @delim varchar(100),

      @token varchar(1000) OUTPUT

    )

    AS

    SET NOCOUNT ON

    DECLARE @spos int

    SET @spos = CHARINDEX( @delim , @parm, 1 )

    IF @spos = 0

      BEGIN

        SET @token = @parm

        SET @parm = ''   

      END

    ELSE

      BEGIN

        SET @token = SubString( @parm, 1, @spos - 1)

        SET @parm = Substring( @parm, @spos + Len(@delim), Len(@parm) - @spos )

      END

    GO

    --------------------------------------------------------------------------------

    -- SAMPLE USAGE

    --------------------------------------------------------------------------------

    DECLARE @s-2 varchar(100), @d varchar(10)

    DECLARE @Fname varchar(20), @Lname varchar(20), @DOB varchar(8)

    SET @s-2 = 'Doe*Jane*12231968'

    SET @d = '*'

    PRINT @s-2

    SET NOCOUNT ON

    EXEC uspGetToken @s-2 OUTPUT, @d, @Fname OUTPUT

    EXEC uspGetToken @s-2 OUTPUT, @d, @Lname OUTPUT

    EXEC uspGetToken @s-2 OUTPUT, @d, @DOB  OUTPUT

    PRINT @Fname

    PRINT @Lname

    PRINT @DOB

    ------------------------------------------------------------------------

    Mike

     

  • Thank you for responding. I really like the method you suggested...I'll give it a shot.

    Demicoq

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply