Replace not working correctly - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Replace not working correctly

Author

Topic

gbeford
Starting Member

9 Posts

Posted - 2009-08-28 : 15:27:17

I have a query where it pulls each day of the week columns... if there is values i would like them returned with a comma separating them... I have that part working ok... however if there is on values in that column it still adds a comma which makes the data look bad.

here is an example of what happens.. could someone show me how to show the commas when needed and not show them if there is no values ?

M,T,W,R,,,S

select
--KMA_LongDesc,
--KMA_ID,
DispatchLocation,
[Sys],
Prin,
Co_Div,
Name,
Title,
OfficeNumber,
Extention,
CellNumber,
replace(ltrim(rtrim(isnull(Monday,'') + ' ' + isnull(Tuesday,'') + ' ' + isnull(Wednesday,'') + ' ' + isnull(Thursday,'') + ' ' + isnull(Friday,'') + ' ' + isnull(Saturday,'') + ' ' + isnull(Sunday,''))),' ',',') as [Day],
StartHour + ' - ' + EndHour as 'Hours',
Notes

from TL_DSRV_KMA_PhoneList
join TL_KMA_Reference
on KMA_ID = TL_KMA_Reference.ID
where KMA_ID = @KMA_ID
and (TL_DSRV_KMA_PhoneList.StopDate >= getdate()
OR TL_DSRV_KMA_PhoneList.StopDate is null)
order by SortOrder

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-28 : 15:42:19

one dirty way...

replace(ltrim(rtrim(isnull(Monday,'X') + ',' + isnull(Tuesday,'X') + ',' + isnull(Wednesday,'X') + ',' + isnull(Thursday,'X') + ',' + isnull(Friday,'X') + ',' + isnull(Saturday,'X') + ',' + isnull(Sunday,'X'))),'X,','') as [Day]

but as madhivanan would say...do the formatting in your front end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-28 : 15:47:39

Use the behavior that concat with null gives null...

replace(ltrim(rtrim(isnull(Monday+ ' ','') + isnull(Tuesday+ ' ','') + isnull(Wednesday+ ' ','') + isnull(Thursday+ ' ','') + isnull(Friday+ ' ','') + isnull(Saturday+ ' ','') + isnull(Sunday,''))),' ',',') as [Day]

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-28 : 16:02:27

rtrim(isnull(Monday + ' ', '') + isnull(Tuesday + ' ' , '') + isnull(Wednesday + ' ', '') + isnull(Thursday + ' ', '') isnull(Friday + ' ', '') + isnull(Saturday + ' ', '') + isnull(Sunday + ' ', '')) as [Day],

N 56°04'39.26"
E 12°55'05.63"

gbeford
Starting Member

9 Posts

Posted - 2009-08-28 : 16:24:13

by doing the above it still seems to give me an X where there is no values ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-28 : 16:26:36

quote:
Originally posted by webfred

Use the behavior that concat with null gives null...

replace(ltrim(rtrim(isnull(Monday+ ' ','') + isnull(Tuesday+ ' ','') + isnull(Wednesday+ ' ','') + isnull(Thursday+ ' ','') + isnull(Friday+ ' ','') + isnull(Saturday+ ' ','') + isnull(Sunday,''))),' ',',') as [Day]

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-29 : 01:28:51

quote:
Originally posted by Peso

rtrim(isnull(Monday + ' ', 'X') + isnull(Tuesday + ' ' , 'X') + isnull(Wednesday + ' ', 'X') + isnull(Thursday + ' ', 'X') isnull(Friday + ' ', 'X') + isnull(Saturday + ' ', 'X') + isnull(Sunday + ' ', 'X')) as [Day],

N 56°04'39.26"
E 12°55'05.63"

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources