Replace Function. Replace comma and all value after comma with Nothing

Question

Replace Function. Replace comma and all value after comma with Nothing

Debbie Edwards

SSCrazy Eights

Points: 9217
More actions
September 29, 2010 at 8:00 am

#229865

Hi,
Say I have the following field in a table (1 field there is a comma in the field 'Name')
Name
Support Centres , Miscellaneous SC
Support Centres , Care of tracey Whitehall
Support Centres , 45757894456
Support Centres , Do not use
Support Centres , 75789999099
can I use a replace function to get rid of the comma and everything after the comma so I just end up with
Support Centres
Support Centres
Support Centres
Support Centres
Support Centres?
thanks in advance
Debbie

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Debbie Edwards (9/29/2010)
Hi,
Say I have the following field in a table (1 field there is a comma in the field 'Name')
Name
Support Centres , Miscellaneous SC
Support Centres , Care of tracey Whitehall
Support Centres , 45757894456
Support Centres , Do not use
Support Centres , 75789999099
can I use a replace function to get rid of the comma and everything after the comma so I just end up with
Support Centres
Support Centres
Support Centres
Support Centres
Support Centres?
thanks in advance
Debbie

Just use the LEFT function, and for the length in the LEFT use the CharIndex function to get the placement of the comma (subtract 1 to not include the comma).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

Debbie Edwards

SSCrazy Eights

Points: 9217

September 29, 2010 at 8:20 am

#1229067

Have you got an example of that at all?

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's an example of what Wayne refered to:

With MySampleData as

(

SELECT 'Support Centres , Miscellaneous SC' As TheName UNION ALL

SELECT 'Support Centres , Care of tracey Whitehall' UNION ALL

SELECT 'Support Centres , 45757894456' UNION ALL

SELECT 'Support Centres , Do not use' UNION ALL

SELECT 'Support Centres , 75789999099' UNION ALL

SELECt 'Data with No Comma'

)

SELECT LEFT(TheName,CHARINDEX(',',TheName) -1) -- minus 1 to remove the comma also

FROM MySampleData

WHERE CHARINDEX(',',TheName) > 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 4

Lowell (9/29/2010)
here's an example of what Wayne refered to:
With MySampleData as
(
SELECT 'Support Centres , Miscellaneous SC' As TheName UNION ALL
SELECT 'Support Centres , Care of tracey Whitehall' UNION ALL
SELECT 'Support Centres , 45757894456' UNION ALL
SELECT 'Support Centres , Do not use' UNION ALL
SELECT 'Support Centres , 75789999099' UNION ALL
SELECt 'Data with No Comma'
)
SELECT LEFT(TheName,CHARINDEX(',',TheName) -1) -- minus 1 to remove the comma also
FROM MySampleData
WHERE CHARINDEX(',',TheName) > 0

fantastic thanks for that. I have tried something like this before but I never understood what the -1 was actually doing. Ive just had a look at the CHARINDEX function too so Im beginning to undertand how it all hangs together.

Thanks for that:-)

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 5

I tried that but got

Msg 536, Level 16, State 5, Line 1

Invalid length parameter passed to the SUBSTRING function.

If I take the -1 out I get records beck but it leaves the comma. very strange.

SELECT LEFT(NAME,CHARINDEX(',',NAME)-1 )FROM Dim_School

Is theer anything Im doing wrong at all?

Debbie

Lowell SSC Guru Points: 323518 More actions · Answer 6

the critical part is adding the WHERE statement...some of your data does not contain a comma...

you want to exclude those items

--items with the offending command and extra data

SELECT LEFT(NAME,CHARINDEX(',',NAME)-1 )

FROM Dim_School

WHERE CHARINDEX(',',Name) > 0

UNION ALL

--items with no comma to worry about

SELECT NAME

FROM Dim_School

WHERE CHARINDEX(',',Name) = 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

Try adding a case statement so it only filters it if there is a comma..here is lowell's code modified a bit.

With MySampleData as

(

SELECT 'Support Centres , Miscellaneous SC' As TheName UNION ALL

SELECT 'Support Centres , Care of tracey Whitehall' UNION ALL

SELECT 'Support Centres , 45757894456' UNION ALL

SELECT 'Support Centres , Do not use' UNION ALL

SELECT 'Support Centres , 75789999099' UNION ALL

SELECt 'Data with No Comma'

)

SELECT

NAME = CASE WHEN CHARINDEX(',',TheName) > 0 THEN LEFT(TheName,CHARINDEX(',',TheName) -1) -- minus 1 to remove the comma also

ELSE TheName

END

FROM MySampleData

WayneS SSC Guru Points: 95460 More actions · Answer 8

You're not using the where clause to not return the rows without commas. This ends up providing a -1 to the LEFT function (which goes to the SUBSTRING function), which is invalid.

If you want all rows returned, even without commas, you'll need to use a CASE statement, and run the portion with the LEFT/CHARINDEX for the rows with commas, and just return the value for rows without the commas.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 9

Ahhhh that makes complete sense. Im going to go for the CASE statement.

Thanks again for the help 🙂

Debbie

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 10

IT WORKED!! 🙂

And I have a much better handle on how it does what it does.

Thanks everyone!

WayneS SSC Guru Points: 95460 More actions · Answer 11

Debbie Edwards (9/29/2010)
IT WORKED!! 🙂
And I have a much better handle on how it does what it does.
Thanks everyone!

(geez, that sounds like you doubted us!)

No problem. Glad to help, and thanks for posting back that things are fine now.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2