Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Unanswered: Comma delimited string handling

    Hello,

    I'm having some problems with string handling. When I increase the @csvLine value after the = to over 1 character it fails. I've tried to debug the sql but I can't work out where its falling over. I think it would be an easy change though. Any help would be greatly appreciated. Thanks The error is

    Syntax error converting the varchar value '82=' to a column of data type int.


    Code:
    DECLARE @idHits varchar(255), @Pos int, @csvLine nvarchar(2000)
    
    SET @csvLine = '115=0&121=1&172=0&207=6&213=11&182=0&131=0'
    
    declare @returnTable TABLE (qfID int , qTitle varchar(255), qUrl varchar(255), qAlt varchar(255), qImp bit, qIcon varchar(255), qExpdate smalldatetime, qPerm bit, qhits varchar(255))
    insert into @returnTable (qfID, qTitle, qUrl, qAlt, qImp, qIcon, qExpdate, qPerm)
    SELECT qfID, qTitle, qUrl, qAlt, qImp, qIcon, qExpdate, qPerm 
    FROM tblQuickfind 
    where qPerm = 1 or qExpDate >= getdate()
    
    SET @Pos = CHARINDEX('&', @csvLine, 1)
    
    declare @x int
    set @x = 0
    while @Pos<>0
    BEGIN
    /*print 'idhits = ' + @Pos*/
    set @x = @x + 1
    SET @idHits = LEFT(@csvLine, @Pos - 1)
    
    print 'idhits = ' + @idHits
    set @Pos = CHARINDEX('=', @idHits,0)
    
    update @returnTable
    set qhits = RIGHT(@idHits,len(@idHits)-@Pos)
    where qfID = LEFT(@idHits,3)
    
    /*print 'hits = ' + RIGHT(@idHits,len(@idHits)-@Pos)*/
    /*print 'id = ' + LEFT(@idHits,3)*/
    
    SET @csvLine = RIGHT(@csvLine, LEN(@csvLine) - @Pos-2)
    /*print 'newLine = ' + @csvLine*/
    SET @Pos = CHARINDEX('&', @csvLine)
    
    END
    
    select * from @returnTable order by qhits desc
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - I can't be bothered debugging that. Personally, this sort of utility function I just get off the internet. Writing one might be illuminating, but is not the best use of time.
    A few here:
    SQL Server Forums - Best split function
    More:
    "sql server" split delimited - Google Search
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Posts
    3
    Thanks for the reply. I should of mentioned that im relatively new to sql and looking at the code you suggested is making me more confused.

    My debuging is telling me that this line is the one with the problem, I think. Its only taking 6 characters off of LEN(@csvLine) when it should be taking 7 at this point because the hits length is higher.

    SET @csvLine = RIGHT(@csvLine, LEN(@csvLine) - @Pos-2)

    Thanks again guys.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - your code is confusing me. Ok - so to try the obvious: why not change it to
    .....@Pos -1)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2009
    Posts
    3
    A simple if around this statement sorted this out, thanks for your time mate, I'm still learning.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry I didn't have time to read your code in depth
    Glad you are sorted
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •