Create stored procedure for parsing Comma Delimited string.

  • Hello, I currently have a stored procedure written for Oracle that I need to port to SQL Server. Basically what I need to do is pass in a string of comma delimited column names that I will loop through and run some basic validity checks on and print out a message if any fail. So I need to parse through the list of columns check each one and print a message and move on the the next one.

    Her e is an example of the string I will be parsing:

    'ADDRESS','CITY_NAME','STATE_CODE','POSTAL_CODE','COUNTRY'

    Any examples would be greatly appreciated.

    Thank you,

    David

    Best Regards,

    ~David

  • You might want to have a look at the TallyTable article referenced in my signature. There's an example with a detailed description.

    Based on that, follow the link in my signature regarding the delimited split function for a link to the latest version I know of.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Another option is to use the split function and load each column name as a record in a table. U would need to first create the split function. Code at bottom.

    Here is some sample code to refference the split function and what it would return

    --here is how u would quesry the function

    select *

    -- , you could put all your validity checks and output messages here as the below example states

    , case when len(item)<5 then 'The column name is less than 5 chars' else 'Looks Good' end Error

    from [fnSplitv2]('''ADDRESS'',''CITY_NAME'',''STATE_CODE'',''POSTAL_CODE'',''COUNTRY''',',')

    --returns. The above checks to make sure their at least 5 chars long.

    item Error

    'ADDRESS'Looks Good

    'CITY_NAME'Looks Good

    'STATE_CODE'Looks Good

    'POSTAL_CODE'Looks Good

    'COUNTRY'Looks Good

    --create split function

    USE [NewBaby]

    GO

    /****** Object: UserDefinedFunction [dbo].[fnSplitv2] Script Date: 01/14/2011 10:34:37 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    create FUNCTION [dbo].[fnSplitv2](

    @sInputList VARCHAR(8000) -- List of delimited items

    , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items

    ) RETURNS @List TABLE (item VARCHAR(8000))

    BEGIN

    DECLARE @sItem VARCHAR(8000)

    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

    BEGIN

    SELECT

    @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),

    @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

    IF LEN(@sItem) > 0

    INSERT INTO @List SELECT @sItem

    END

    IF LEN(@sInputList) > 0

    INSERT INTO @List SELECT @sInputList -- Put the last item in

    RETURN

    END

  • search around, there are a couple of ready solutions around

    imo, Jeff Moden offers the best split function for strings with less than 4000 chars and Adam Machanic CLR split functions works best for big strings(varchar(max))

    --
    Thiago Dantas
    @DantHimself

  • I have used some code from your referenced articles. Below is the code I have so far.

    DECLARE

    @Parameter VARCHAR(8000),

    @tabName VARCHAR(100),

    @colName VARCHAR(100),

    @sqlStmt VARCHAR(4000),

    @cnt INT

    SET @Parameter = REPLICATE('OUTLET2_ADDRESS,CITY_NAME,STATE_CODE,POSTAL_CODE,COUNTRY',1)

    SET @tabName = 'outlet'

    SET NOCOUNT ON

    --===== Create a table to store the results in

    DECLARE @Elements TABLE

    (

    Number INT IDENTITY(1,1), --Order it appears in original string

    Value VARCHAR(50)--The string value of the element

    );

    --===== Add start and end commas to the Parameter so we can handle -- single elements

    SET @Parameter = ','+@Parameter +','

    INSERT INTO @Elements

    (Value)

    SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)

    FROM Tally

    WHERE N < LEN(@Parameter)

    AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma

    SET @cnt = @@ROWCOUNT

    PRINT 'Row Count:' + CAST(@cnt AS VARCHAR)

    I need to at this point iterate through each value in @elements, which are column names in the table specified in @tabName to see if it is null and print a message if it is.

    Best Regards,

    ~David

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

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