March 31, 2009 at 11:20 pm
01008,03072,0045,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-17
01008,03072,0046,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,311
01008,03072,0048,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,12336
01008,03072,0049,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,986
01008,03072,0056,C,,,,,,,-1057
01008,03072,0057,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5320
01008,03072,0069,C,,,,,,,-4109
01008,03072,0071,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2180
01008,03072,0078,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1
March 31, 2009 at 11:42 pm
Padam Kumar Tripathi (3/31/2009)
01008,03072,0045,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-1701008,03072,0046,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,311
01008,03072,0048,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,12336
01008,03072,0049,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,986
01008,03072,0056,C,,,,,,,-1057
01008,03072,0057,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5320
01008,03072,0069,C,,,,,,,-4109
01008,03072,0071,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2180
01008,03072,0078,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1
Like this:
Replace(Replace(Replace(Replace(strCol, ',,,,,,,', ','), ',,,,,', ','), ',,,', ','), ',,', ',')
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2009 at 11:49 pm
declare @a varchar(50), @b-2 varchar(50)
Set @a = '01008,03072,0045,R,,,,,,,,,-17'
Set @b-2 = replace(@a, ',,', '')
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 1, 2009 at 12:10 am
Good job, Phil. For some reason I thought that SQL Replace() didn't rescan its replacements. Hmm, must be getting my languages mixed up. 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2009 at 12:31 am
Haha - I know what you mean. I wasn't sure until I tried it either 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 1, 2009 at 9:22 am
Phil Parkin (3/31/2009)
declare @a varchar(50), @b-2 varchar(50)Set @a = '01008,03072,0045,R,,,,,,,,,-17'
Set @b-2 = replace(@a, ',,', '')
Phil, this works fine ONLY if the no. of consecutive commas is an odd number.
Let's see it...
SELECTREPLACE( T, ',,', ',' )
FROM(
SELECT'01008,03072,0045,R,,,,,,,,,-17' AS T -- odd no. of consecutive commas
UNION ALL
SELECT'01008,03072,0045,R,,,,,,,,,,-17' AS T -- even no. of consecutive commas
UNION ALL
SELECT'01008,,03072,,,0045,R,-17' AS T -- mix even/odd no. of consecutive commas
UNION ALL
SELECT'01008,03072,0045,R,-17' AS T -- no consecutive commas
) A
Now here is the my version of it...
SELECTREPLACE( REPLACE( REPLACE( REPLACE( T, ',,', 'Ñ,' ), ',Ñ', '' ), 'Ñ', '' ), ',,', ',' )
FROM(
SELECT'01008,03072,0045,R,,,,,,,,,-17' AS T -- odd no. of consecutive commas
UNION ALL
SELECT'01008,03072,0045,R,,,,,,,,,,-17' AS T -- even no. of consecutive commas
UNION ALL
SELECT'01008,,03072,,,0045,R,-17' AS T -- mix even/odd no. of consecutive commas
UNION ALL
SELECT'01008,03072,0045,R,-17' AS T -- no consecutive commas
) A
The logic is quite simple, first replace ",," with "Ñ," (note that, I've chosen a UNICODE character where the probability of it appearing is less), then replace "Ñ" with "" and finally ",," with ","
Let's see the execution
For ODD no. of consecutive commas
01008,03072,0045,R,,,,,,,,,-17 > 01008,03072,0045,RÑ,Ñ,Ñ,Ñ,,-17-- Replace ",," WITH "Ñ,"
01008,03072,0045,RÑ,Ñ,Ñ,Ñ,,-17 > 01008,03072,0045,RÑ,,-17 -- Replace "Ñ" WITH ""
01008,03072,0045,RÑ,,-17 > 01008,03072,0045,RÑ,-17 -- Replace ",," WITH ","
For EVEN no. of consecutive commas
01008,03072,0045,R,,,,,,,,,,-17 > 01008,03072,0045,RÑ,Ñ,Ñ,Ñ,Ñ,-17 -- Replace ",," WITH "Ñ,
01008,03072,0045,RÑ,Ñ,Ñ,Ñ,Ñ,-17 > 01008,03072,0045,R,,,,,-17 -- Replace "Ñ" WITH ""
01008,03072,0045,R,,,,,-17 > 01008,03072,0045,R,-17-- Replace ",," WITH ","
--Ramesh
April 1, 2009 at 9:04 pm
... and I thought I had tested that! Well, it was early in the morning :doze:
Nice approach Ramesh - great use of logic.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 2, 2009 at 1:03 am
Phil Parkin (4/1/2009)
... and I thought I had tested that! Well, it was early in the morning :doze:Nice approach Ramesh - great use of logic.
Phil
...Sometimes, my mind works well in the nights!!!!:-D
--Ramesh
April 3, 2009 at 11:15 pm
Very easiest way for replacing comma from value
select replace (column_name, ',', '') from table_name
April 4, 2009 at 12:13 am
select substring(column_name, 1, charindex(',,', column_name) -1) +
case
when replace(substring (column_name, charindex(',,', column_name), len(column_name)),',','') = ' ' then ''
when replace(substring (column_name, charindex(',,', column_name), len(column_name)),',','') <> ' ' then
+','+replace(substring (column_name, charindex(',,', column_name), len(column_name)),',','')
end as new_column_name
from table_name
April 4, 2009 at 12:18 am
I like Ramesh's solution. Your first solution replaces all commas ... this is not the requirement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 4, 2009 at 1:28 am
Hi,
Try this on this way....
create table #table
(
row1 varchar(1000)
)
insert into #table select '01008,03072,0045,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-17'
insert into #table select '01008,03072,0046,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,311'
insert into #table select '01008,03072,0048,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,12336'
insert into #table select '01008,03072,0049,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,986'
insert into #table select '01008,03072,0056,C,,,,,,,-1057'
insert into #table select '01008,03072,0057,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5320'
insert into #table select '01008,03072,0069,C,,,,,,,-4109'
insert into #table select '01008,03072,0071,R,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2180'
insert into #table select '01008,03072,0078,R,,'
select substring(row1, 1, 18) +
case when right(row1,charindex(',' ,reverse(row1))) = ',' then '' else right(row1,charindex(',' ,reverse(row1))) end as Result,
* from #table
April 4, 2009 at 2:06 am
Seems to work with the supplied data, but does not work with all occurrences of multiple commas, eg
insert into #table select '01008,,,,,,,,,,,,,,03072,0078,R,,'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 4, 2009 at 3:34 am
Nice one, but frankly i m not satisfied with Mr. Ramesh answers, because i run and tried the query and found that sometimes it shows incorrect output. Sorry Boss...
April 4, 2009 at 5:44 am
vijays (4/4/2009)
Nice one, but frankly i m not satisfied with Mr. Ramesh answers, because i run and tried the query and found that sometimes it shows incorrect output. Sorry Boss...
Instead of throwing away my solution, Mr. Vijay, can you tell or even show us where does it fail? Or you can even show us another solution to the problem, if you have one?
--Ramesh
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply