Exact Target SQL find two values within a string

Question

We have a value stored in a string in a table and we need to extract 2 values from it into 2 new fields.

The field name is linkname and it contains a value like 'VALUE1-VALUE2-52'. We need to pull VALUE1 and VALUE2 out of the string and insert them into a second table.

I have tried using CHARINDEX but it doesn't work (as usual with SFMC i do not get any error message). Does anyone know how I can extract these 2 values using a query?

This should extract the first value but it fails:

select LEFT(linkname,CHARINDEX('-',linkname)-1) from mytable;

Anyone know how to extract the values? e.g - string value 'APPLE-ORANGES-52'. Return APPLE as VALUE1 and ORANGES as VALUE2.

Answer 1

I've had passed success at using both Gaurav's ampscript approach and Arvind's SQL approach.

You can use Case statements to make Arvind's SQL query more fault tolerant.

SELECT 
 linkname
, CASE
    WHEN CHARINDEX('-',linkname) > 0 THEN Substring(linkname,1,CHARINDEX('-', linkname)-1)
    ELSE linkname
  END as HEREISVALUE1
, CASE
    WHEN CHARINDEX('-',linkname) > 0 THEN 
       CASE 
          WHEN CHARINDEX('-', Substring(linkname,CHARINDEX('-',linkname)+1,len(linkname))) > 0 THEN Substring(Substring(linkname,CHARINDEX('-',linkname)+1,len(linkname)),1, CHARINDEX('-', Substring(linkname,CHARINDEX('-',linkname)+1,len(linkname)))-1 )
          ELSE Substring(linkname,CHARINDEX('-',linkname)+1,len(linkname))
       END
    ELSE ''
  END as HEREISVALUE2
from [#TempTable]

Input:

|linkname        |
|:-------------- |
|APPLE-ORANGES-52|
|APPLE           |
|APPLE-ORANGE    |
|                |

Output:

|linkname        |HEREISVALUE1    |HEREISVALUE2    |
|:-------------- |:-------------- |:-------------- |
|APPLE-ORANGES-52|APPLE           |ORANGE          |
|APPLE           |APPLE           |                |
|APPLE-ORANGE    |APPLE           |ORANGE          |
|                |                |                |

Answer 2

Rather than handling this in the query itself, you can use the ampscript utility functions

IndexOf("Your String", "-") will give you the indices of the hyphens Then use those indices with Substring("Your String", "-") to get the 2 relevant sub-strings

Based on the example above

IndexOf("APPLE-ORANGES-52", "-") will return 6

SubString("APPLE-ORANGES-52", 0, 5) will return Apple SubString("APPLE-ORANGES-52", 5+1) will return Oranges-52

Run the same logic through 'Oranges-52'

Answer 3

You can use SQL and a numbers (tally) helper table to split them out via a query activity

So something like this

SELECT 
email
, SUBSTRING('|' + Product + '|', n + 1, CHARINDEX('|', '|' + Product + '|', n + 1) - n - 1) AS product
FROM Nums
JOIN DE 
  ON (SUBSTRING('|' + Product + '|', n, 1 ) = '|' 
      AND n < LEN('|' +Product + '|')_

where Nums is a Numbers table with 1 column (n) that contains values from 1 to 10000 and DE is your DE and the Product is column you are splitting. Am using the pipe delimiter (|) but you can change the query to use your delimiter (-)