-1

I read the post: Turning a Comma Separated string into individual rows

And really like the solution:

SELECT A.OtherID,  
     Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (     SELECT OtherID,  
         CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Table1
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);

But it did not work when I tried to apply the method in Teradata for a similar question. Here is the summarized error code: select failed 3707: expected something between '.' and the 'value' keyword. So is the code only valid in SQL Server? Would anyone help me to make it work in Teradata or SAS SQL? Your help will be really appreciated!

Community
  • 1
  • 1
SLG
  • 11
  • 1
  • 2
  • That is definitely a sql-server only function as written. Would you mind posting what your data looks like now, and how you would want the result to look from the query? Perhaps then we can whip up some SQL or dig up a function that will do the job in Teradata. – JNevill Mar 17 '15 at 17:35
  • What did you try to do to fix it? What have you attempted? – UpAndAdam Mar 17 '15 at 17:54
  • @JNevill Hi JNevill & UpAndAdam, Thank you both for your willingness to help. I found another way to get around it in SAS. I was trying to split a field with multiple descriptions divided by "#" to separate rows. Here is the SAS code that worked: data Example2; set Example ; /* Count number of times the delimiter occurs */ count= count(Description, '#'); /* Output each Description to a separate observation */ do i=1 to count; Description2 = scan(Description,i,'#'); output; end; run; – SLG Mar 26 '15 at 17:55

1 Answers1

3

This is SQL Server syntax.

In Teradata there's a table UDF named STRTOK_SPLIT_TO_TABLE, e.g.

SELECT * FROM dbc.DatabasesV AS db
JOIN 
 (
   SELECT token AS DatabaseName, tokennum
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, 'dbc,systemfe', ',')
        RETURNS (outkey INTEGER,
                 tokennum INTEGER,
                 token VARCHAR(128) CHARACTER SET UNICODE)
              ) AS d 
 ) AS dt
ON db.DatabaseName  = dt.DatabaseName
ORDER BY tokennum;

Or see my answer to this similar question

Community
  • 1
  • 1
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Hi dnoeth, thank you so much for your help! Though I found a way to do it is SAS, the trick you showed me in Teradata will come in handy in the future. Thanks! – SLG Mar 26 '15 at 17:40