Post reply

Querying Comma Delimited Data in Field

  • February 18, 2015 at 12:08 pm

    #315483

    SSC,

    I am inserting data to SQL 2005 from an HTTP Post, the minimum usable data is 120 characters long (Sample: Tag:E200 32E2 F2B3 A4B1 252B CE92, Disc:2015/02/17 15:47:44.215, Last:2015/02/17 15:47:44.654, Count:2, Ant:1, Proto:2). I need to query the comma delimited data to extract the EPC E200 32E2 F2B3 A4B1 252B CE92 then convert to E20032E2F2B3A4B1252BCE92 by removing spaces. Sometimes the string length is 1200 meaning 10 items has passed through the portal.

    SELECT TAGS, LEN(CAST(TAGS AS VARCHAR(MAX))) AS LENGTH, LEN(CAST(TAGS AS VARCHAR(MAX))) / 120 AS TAG_COUNT, DATEREAD

    FROM DUFRFID

    Tells me how many RFID tagged units within that string passed through my choke point. I then will join that EPC to another table to get product description. Any help is greatly appreciated.

    Thanks,

    kabaari

  • February 18, 2015 at 12:19 pm

    #1778448

    kabaari (2/18/2015)

    SSC,

    I am inserting data to SQL 2005 from an HTTP Post, the minimum usable data is 120 characters long (Sample: Tag:E200 32E2 F2B3 A4B1 252B CE92, Disc:2015/02/17 15:47:44.215, Last:2015/02/17 15:47:44.654, Count:2, Ant:1, Proto:2). I need to query the comma delimited data to extract the EPC E200 32E2 F2B3 A4B1 252B CE92 then convert to E20032E2F2B3A4B1252BCE92 by removing spaces. Sometimes the string length is 1200 meaning 10 items has passed through the portal.

    SELECT TAGS, LEN(CAST(TAGS AS VARCHAR(MAX))) AS LENGTH, LEN(CAST(TAGS AS VARCHAR(MAX))) / 120 AS TAG_COUNT, DATEREAD

    FROM DUFRFID

    Tells me how many RFID tagged units within that string passed through my choke point. I then will join that EPC to another table to get product description. Any help is greatly appreciated.

    Thanks,

    kabaari

    In order to do this you need to split your string. See the link referenced in my signature about splitting strings.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 2 posts - 1 through 1 (of 1 total)

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