0

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.

3

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          |
|                |                |                |
  • Yeah that works really well - the error handling has meant we do not need to worry so much about records that do not comply. – BDOB83 Jan 19 '16 at 6:02
3

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'

1

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 (-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.