SQL String functions

Question

This is an issue that I've put way too much time into.

I'm passing in a string as

@OrderString varchar(255) = '1=1;|2=|3=|4=1;|5=|'

The first number is the item I'd like to order I'll save that in @ItemNum the second number after the = is the quantity(will be stored as @ItemQuat) . The quantity can be anything from 1 to 1000. If zero is ordered then it will just be the item number and =.

The question is how can I dynamically pull these values? I only want the values that are greater then 0.

I've messed around with it for hours and I'm not sure if I have to update the logic that builds the string or is it possible to pull the correct values using string functions in sql.

Example of what I currently have:

--Obviously this won't work because it will always pull 1 regardless if the amount ordered is 0  
set @ItemNum = substring(@OrderString, 1, charindex('=', @OrderString, 1)-1)

--This is currently wrong too because its pulling the quant for item 1
set @ItemQuat = substring(@OrderString, charindex('=',@OrderString,1)+1, charindex('|',@OrderString,1)-charindex('=',@OrderString,1)-1)

I'm looping through and concatenating the string after pulling the first value, etc.

Any help would be appreciated!

where is this string coming from? if that data's in a table somewhere, you'd be far better off re-querying that table and getting raw "unstringged" data rather than trying to tear apart this string version later. — Marc B
– Marc B, Commented Feb 15, 2014 at 22:23
Its being passed in from xml. I'm checking to see if the quantity ordered is available before placing the order. So I'm not able to pull the string from a table :( — user3314567
– user3314567, Commented Feb 15, 2014 at 22:28

Jorge Candeias · Accepted Answer · 2014-02-16 00:36:58Z

This single query will convert that string into a table result with columns [ItemNumber] and [ItemValue], plus only return rows where the value is greater than zero. Does this work for you?

DECLARE @OrderString VARCHAR(MAX) = '1=1;|2=|3=|4=1;|5=|';

WITH Step0 AS
(
    SELECT
        String = REPLACE(@OrderString, ';', '')
),
Step1 AS
(
    SELECT
        Block = SUBSTRING(String, 1, CHARINDEX('|', String) - 1),
        String = SUBSTRING(String, CHARINDEX('|', String) + 1, LEN(String))
    FROM
        Step0

    UNION ALL

    SELECT
        Block = SUBSTRING(String, 1, CHARINDEX('|', String) - 1),
        String = SUBSTRING(String, CHARINDEX('|', String) + 1, LEN(String))
    FROM
        Step1
    WHERE
        LEN(String) > 0
),

Step2 AS
(
    SELECT
        ItemNumber = SUBSTRING(Block, 1, CHARINDEX('=', Block) - 1),
        ItemValue = SUBSTRING(Block, CHARINDEX('=', Block) + 1, LEN(Block))
    FROM
        Step1
),

Step3 AS
(
    SELECT
        ItemNumber = CAST(ItemNumber AS INT),
        ItemValue = CAST(ItemValue AS INT)
    FROM
        Step2
    WHERE
        CAST(ItemValue AS INT) > 0
)

SELECT
    *
FROM
    Step3;

PS.: Found the exercise curious so thought I'd use it for a post, hope you don't mind:

https://tangodude.wordpress.com/2014/02/16/t-sql-extracting-serialized-data-from-string-in-one-go/

Karl Kieninger · Accepted Answer · 2014-02-16 01:49:35Z

If it is "passed in from XML" you would probably be much better off passing in the XML rather than the string. The XML processing into what you want would be quite simply.

You could even do something ugly to get the string back into XML:

DECLARE @OrderString VARCHAR(MAX) = '1=1;|2=|3=|4=1;|5=|';

DECLARE @xml XML
SELECT @xml = CAST('<order_line><item_id>'
             +REPLACE(REPLACE(REPLACE(STUFF(@OrderString,LEN(@OrderString),1,''),';',''),'|' ,'</quantity></order_line><order_line><item_id>'),'=','</item_id><quantity>')
             +'</quantity></order_line>' AS XML)

And then it's really simple:

SELECT c.value('(item_id)[1]', 'int') item_id
      ,c.value('(quantity)[1]', 'int') quantity
  FROM @xml.nodes('/order_line') T(c)

Collectives™ on Stack Overflow

SQL String functions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related