SQLTeam.com | Weblogs | Forums

Comma between string in SQL


#1

Hello SQL Expert,

I am trying to split the string value between string as Field_1 and Field_2, but some of the value has no comma. So any values that do not have comma will be skipped.

How do I string manipultaion in SQL?

So for example:

Column_A
ABC,DEF
ACD,FGH,
ADFGFH,CV
HHHH,CG
HHHKKK01
JUHUHG1112

So I need to split the value of Column A as
Column_A FIELD_1 FIELD_2
ABC,DEF ABD DEF
ACD,FGH ACD FGH
AD,GGFG AD GGFG
HHHH,CG HHHH CG
HHKF2231
JUH00122

Thanks guys


#2
SELECT Column_A,
    CASE WHEN Column_A_comma = 0 THEN '' 
         ELSE LEFT(Column_A, Column_A_comma - 1) END AS FIELD_1,
    CASE WHEN Column_A_comma = 0 THEN '' 
         ELSE SUBSTRING(Column_A, Column_A_comma + 1, 8000) END AS FIELD_2
FROM table_name
CROSS APPLY (
    SELECT CHARINDEX(',', Column_A) AS Column_A_comma
) AS assign_alias_names

#3

Thank you Scott. It worked!