CODEWITHSUNDEEP
sqlsql-serverstringsql-server-2008substring
user3314567
user3314567

Reputation: 23

SQL String functions

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!

Upvotes: 2

Views: 546

Answers (2)

Karl Kieninger
Karl Kieninger

Reputation: 9129

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)

Upvotes: 0

Jorge Candeias
Jorge Candeias

Reputation: 720

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/

Upvotes: 2

Related Questions