Remove commas

Question

Remove commas

craig.jenkins

SSCommitted

Points: 1643
More actions
September 28, 2018 at 2:10 am

#364221

Hi Guys,
Is there an easy way to remove commas. There are several commas which occur in the results but can be in any column.
Thanks
SELECT
CONVERT(CHAR(12), DateIn, 109) AS DateIn
,Booking
,RegNo
,CustName
,Model
,ServText
,Phone
,Team
,CONCAT(Facilities, '-' , IntRef) as [Facilities]
, CONCAT(TIMEIN, '-' , TIMEOUT) as [In-Out]
,TimeOut - TimeIn AS Hrs
FROM Bookings
WHERE DATEIN >= GETDATE()+6 AND DATEIN < GETDATE()+7
AND DEALER = 'CA'
AND SERVTEXT NOT LIKE '%DIAG%'
AND CONCAT(Facilities, '-' , IntRef) LIKE '%WAIT%'
ORDER BY RegNO

Viewing 5 posts - 1 through 4 (of 4 total)

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

craig.jenkins - Friday, September 28, 2018 2:10 AM
Hi Guys,
Is there an easy way to remove commas. There are several commas which occur in the results but can be in any column.
Thanks
SELECT
CONVERT(CHAR(12), DateIn, 109) AS DateIn
,Booking
,RegNo
,CustName
,Model
,ServText
,Phone
,Team
,CONCAT(Facilities, '-' , IntRef) as [Facilities]
, CONCAT(TIMEIN, '-' , TIMEOUT) as [In-Out]
,TimeOut - TimeIn AS Hrs
FROM Bookings
WHERE DATEIN >= GETDATE()+6 AND DATEIN < GETDATE()+7
AND DEALER = 'CA'
AND SERVTEXT NOT LIKE '%DIAG%'
AND CONCAT(Facilities, '-' , IntRef) LIKE '%WAIT%'
ORDER BY RegNO

Define "easy way to remove commas". Easy depends on whom you talk to. There's always the obvious use of the REPLACE function. It's not regex, but if all you are doing is replacing ALL the commas in a given column, you don't need regex and can just use REPLACE. The syntax is drop dead easy:

REPLACE(ColumnToBeFixed, StringToBeReplaced, StringReplacementText)

The StringReplacementText can always be an empty string (in T-SQL, that''s two consecutive single quotes.)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

craig.jenkins SSCommitted Points: 1643 More actions · Answer 2

thanks for that - i really need to replace all the commas in all columns i'm assuming this is a little harder to do

craig.jenkins SSCommitted Points: 1643 More actions · Answer 3

Actually, dont worry i'll just use that for each column. Many thanks for your help

sgmunson SSC Guru Points: 110640 More actions · Answer 4

craig.jenkins - Friday, September 28, 2018 6:55 AM
Actually, dont worry i'll just use that for each column. Many thanks for your help

That's what you would need to do anyway, unless you had the means to edit the original source data before it gets into the tables. It could also be done "on the way in", in whatever ETL tool might be in use... SSIS in particular makes such things fairly easy as well, and it would involve a nearly identical technique.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)