remove commas from a strings

Question

I have two rows in column like this:

So I want to remove first comma from first one and remove first two from 2nd one. I want output like this egxample:

Josiah Tongari Street, Labone, Accra Metropolitan, Greater Accra, Ghana 2.Accra, Greater Accra, Ghana

Thanks

use regex. thats is a string manipulation, better do that with restitution tool rather than DB — Steven, Jul 26 '17 at 06:11
SELECT REPLACE(','+ fulladdress + ',',',,','') from businessmaster where fulladdress like ',%' or fulladdress like ', ,%' — Sandeep Singh, Jul 26 '17 at 06:12
i have right the above code but it can not replace 2 condition — Sandeep Singh, Jul 26 '17 at 06:13
Have a look at [this post](https://stackoverflow.com/questions/8545367/trim-commas-with-mysql). You can use `TRIM(LEADING ',' FROM [yourString])` in MySQL. — diiN__________, Jul 26 '17 at 06:14
in the above code it replace first comma but it can not replace first two comma. — Sandeep Singh, Jul 26 '17 at 06:15

score 1 · Answer 1 · answered Jul 26 '17 at 08:31

First, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
After reading that, you should normalize your database.

If you can't normalize your database, then you can use STUFF and PATINDEX to remove the first commas.

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T AS TABLE
(
    col varchar(100) 
)

INSERT INTO @T VALUES 
('a, b,c'), 
(', d, e,f'), 
(', , g, h,i, j')

The query:

SELECT  STUFF(col, 1, PATINDEX('%[^, ]%', col)-1, '') As col
FROM @T

Results:

col
a, b,c
d, e,f
g, h,i, j

[Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) — Zohar Peled, Jul 26 '17 at 08:40

1 Answers1