CODEWITHSUNDEEP
sqlsql-serverstringsplit
CROW
CROW

Reputation: 13

Insert comma into varchar substring

I would like to split a string by a comma and space. I am using SQL Server.
Not sure if I should use charindex/String_Split/etc.

    SELECT  HCPCS,
            LEFT(HCPCS,5) as "CPT",                 
            SUBSTRING(HCPCS,6,8) as "MOD"                               

    FROM    MyTable

Output

 HCPCS        CPT        MOD
 99214POTC    99214      POTC

Desired Output

HCPCS         CPT        MOD
99214POTC     99214      PO, TC

Upvotes: 1

Views: 524

Answers (2)

ps2340256
ps2340256

Reputation: 51

You can achieve your goal using concatenation operator (+) in SQL. Below code will give expected result. You must be misunderstood the SUBSTRING function, the third parameter of the SUBSTRING function represents length not the position.

select HCPCS, LEFT(HCPCS,5) as "CPT", SUBSTRING(HCPCS,6,2) + ',' + SUBSTRING(HCPCS,8,2) as "MOD";

Hope this helps.

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

you can use STUFF to place a comma inside a string.

Select STUFF('Test',3, 0,', ')

in your case it might be

Select STUFF(SUBSTRING(HCPCS,6,8), 3, 0, ', ')

Upvotes: 2

Related Questions