April 4, 2009 at 5:45 am
Phil Parkin (4/4/2009)
I like Ramesh's solution. Your first solution replaces all commas ... this is not the requirement.
Thanks Phil for supporting the only solution, till now.
--Ramesh
April 4, 2009 at 6:20 am
Ramesh (4/4/2009)
Phil Parkin (4/4/2009)
I like Ramesh's solution. Your first solution replaces all commas ... this is not the requirement.Thanks Phil for supporting the only solution, till now.
Easy for me to support the best. Unless someone can show that your method is sometimes problematic, this thread should be closed - your solution is elegant and does the job.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 4, 2009 at 7:37 am
Ramesh (4/4/2009)
Phil Parkin (4/4/2009)
I like Ramesh's solution. Your first solution replaces all commas ... this is not the requirement.Thanks Phil for supporting the only solution, till now.
Uh, no. As far as I know, my solution works just fine and was the very first reply. and is much more general since it does not depend on knowing ahead of time which characters are in use.
[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 4, 2009 at 8:10 am
RBarryYoung (4/4/2009)
Ramesh (4/4/2009)
Phil Parkin (4/4/2009)
I like Ramesh's solution. Your first solution replaces all commas ... this is not the requirement.Thanks Phil for supporting the only solution, till now.
Uh, no. As far as I know, my solution works just fine and was the very first reply. and is much more general since it does not depend on knowing ahead of time which characters are in use.
My apologies Barry, I didn't see your solution.
After looking at your code and doing a little bit of math, I came to a conclusion that it does not work when the no. of consecutive commas are (7n - 1) where n is a multiple of 4.
Lets see it for n = 4, i.e. consecutive commas = 27
SELECT REPLACE( REPLACE( REPLACE( REPLACE( T, ',,,,,,,', ',' ), ',,,,,', ',' ), ',,,', ',' ), ',,', ',' ) AS BarryVersion,
REPLACE( REPLACE( REPLACE( REPLACE( T, ',,', 'Ñ,' ), ',Ñ', '' ), 'Ñ', '' ), ',,', ',' ) AS RameshVersion
FROM (
SELECT '01008,03072,0045,R' + REPLICATE( ',', 27 ) + '-17' AS T
UNION ALL
SELECT '01008,03072,0045,R' + REPLICATE( ',', 55 ) + '-17' AS T
UNION ALL
SELECT '01008,03072,0045,R' + REPLICATE( ',', 83 ) + '-17' AS T
) A
-- Math behind Barry''s solution for 27 consecutive commas
-- 7776 > 1116
-- 54 > 14
-- 32 > 12
-- 21 > 11 > 2
--Ramesh
April 4, 2009 at 8:16 am
Barry,
Well, I know it can easily extended to the next or higher level by adding another REPLACE of 9 commas or even 11 commas, but I just wanted to let the OP and other posters to make note of it.
--Ramesh
April 4, 2009 at 9:05 am
Ramesh (4/4/2009)
Barry,Well, I know it can easily extended to the next or higher level by adding another REPLACE of 9 commas or even 11 commas, but I just wanted to let the OP and other posters to make note of it.
Good point. There is always an upper limit with this approach, however, since the OP problem clearly had an upper limit of 29, my solution should have worked up to 29. Sorry, I should have checked.
I believe that this is the most time & finger efficient solution to the OP's stated problem:
SELECT REPLACE( REPLACE( REPLACE( REPLACE( T, ',,,,', ',' ), ',,,', ',' ), ',,', ',' ), ',,', ',' )
And this time I did check and this solution is good up to 34, well beyond the OP's limit.
[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 4, 2009 at 10:08 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...
Post the code that shows the exception.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2009 at 10:58 am
Ramesh (4/4/2009)
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?
I've seen this solution before, but with only 3 replaces. The problem with that is it can make the working operands grow larger than the original, possibly violating the max length of the data type. Your's has no such fault. You traded a tiny bit of speed with the extra REPLACE for absolute data integrity and it has virtually no limits. Very, very nicely done.
My only suggestion is that the character you chose could actually show up in things that people type (anything could show up in things that are generated by computer)... just to decrease the odds of using a character that appears in the original text, you may want to use a "non-printable" character... perhaps something like CHAR(1).
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2010 at 11:57 am
RBarryYoung (4/1/2009)
Good job, Phil. For some reason I thought that SQL Replace() didn't rescan its replacements. Hmm, must be getting my languages mixed up. 🙁
Hi RBarryYoung,
you were right!! SQL do NOT rescan its replacement!
Actually the Phil Parkin's code replace ',,' with '' (empty string) and NOT with ',' (single comma)... and this is the reason why that code works only with an odd number of commas (as Ramesh Saive said!).
Bye,
Flavio.
EDIT: SORRY to all! I've seen only page 1 of this thread and didn't see that you already said this! Sorry again! Bye, Flavio.
February 25, 2010 at 12:56 pm
Nested REPLACE works just fine:
print 'Load Test data with all strings of Commas from 1 to 8000'
select
NUMBER,
Commas = convert(varchar(8000),replicate(',',NUMBER))
into
#t
from
-- Number Table Function available here:
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
F_TABLE_NUMBER_RANGE(1,8000)
order by
NUMBER
go
print 'Test Replaces'
select
*
from
(
select
NUMBER,
Commas =
replace(replace(replace(replace(replace(replace(replace(Commas
,',,,,,,,,,,',',')
,',,,,,,,,,,',',')
,',,,,,,,,,,',',')
,',,',',')
,',,',',')
,',,',',')
,',,',',')
from
#t
) a
where
Commas <> ','
order by
NUMBER
go
drop table #t
Results:
Load Test data with all strings of Commas from 1 to 8000
(8000 row(s) affected)
Test Replaces
NUMBER Commas
----------- -------------------------------------
(0 row(s) affected)
February 26, 2010 at 2:44 am
RBarryYoung (4/4/2009)
Ramesh (4/4/2009)
Barry,Well, I know it can easily extended to the next or higher level by adding another REPLACE of 9 commas or even 11 commas, but I just wanted to let the OP and other posters to make note of it.
Good point. There is always an upper limit with this approach, however, since the OP problem clearly had an upper limit of 29, my solution should have worked up to 29. Sorry, I should have checked.
I believe that this is the most time & finger efficient solution to the OP's stated problem:
SELECT REPLACE( REPLACE( REPLACE( REPLACE( T, ',,,,', ',' ), ',,,', ',' ), ',,', ',' ), ',,', ',' )
And this time I did check and this solution is good up to 34, well beyond the OP's limit.
It gets much better than a mere 34 with only two more REPLACEs, and it's darned fast too:
SELECT RowID,
CommaString,
CommasFound = DATALENGTH(CommaString),
Result,
CommasLeft = DATALENGTH(Result)
FROM (
SELECT RowID, CommaString,
Result = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CommaString, ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,', ','), ',,,,,,,,,,,,,,,,', ','), ',,,,,,,,', ','), ',,,,', ','), ',,,', ','), ',,', ',')
FROM (
SELECT d.RowID, CommaString = REPLICATE(CAST(',' AS VARCHAR(MAX)), d.RowID)
FROM (
SELECT TOP 12000 RowID = ROW_NUMBER() OVER(ORDER BY a.NAME)
FROM master.sys.all_columns a, master.sys.all_columns b
) d
) s
) r
WHERE DATALENGTH(Result) > 1
-- 4 REPLACEs (max 8 commas replaced with 1): fails at 39
-- 5 REPLACEs (max 16 commas replaced with 1): fails at 399
-- 6 REPLACEs (max 32 commas replaced with 1): fails at 11807
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 26, 2010 at 6:55 am
Hi,
declare @a varchar(50), @b-2 varchar(50)
Set @a = '01008,03072,0045,R,,,,,,,,,-17'
Set @b-2 = replace(@a, ',,', '')
I have run it is returning single comma (,). In between 'R' and '-17' have odd numer of comma (i.e. 9 times). If it is even number of comma (like 10 or 8 times)then please help me how i will get the same result.
Set @a = '01008,03072,0045,R,,,,,,,,,,-17'
Thanks & Regards,
-Prabir
July 20, 2020 at 2:37 pm
I have a simple way to remove multiple commas in the whole string.Here is the simple one to suffice all the below cases:
1> Removing multiple commas in starting of the string
2> Removing multiple commas at the end of the string
3> Removing multiple commas to single comma in the middle of the string
select REGEXP_REPLACE(REGEXP_REPLACE(',,LE,,EN,,A,,,','^,*|,*$',''),',{1,}', ', ')
output :
LE, EN, A
July 20, 2020 at 2:44 pm
I have a simple way to remove multiple commas in the whole string.Here is the simple one to suffice all the below cases:
1> Removing multiple commas in starting of the string
2> Removing multiple commas at the end of the string
3> Removing multiple commas to single comma in the middle of the string
select REGEXP_REPLACE(REGEXP_REPLACE(',,LE,,EN,,A,,,','^,*|,*$',''),',{1,}', ', ')
output :
LE, EN, A
Hello and welcome to the forum.
As far as I know, REGEXP_REPLACE() is not a valid SQL Server function.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 20, 2020 at 3:33 pm
In 2016+ it could be done like this
drop function if exists dbo.fnCommaOnce;
go
create function dbo.fnCommaOnce(
@comma_str varchar(100))
returns table as
return
with exp_str(n, t) as (
select n,
substring(@comma_str, n, 1)
from
dbo.fnTally(1, len(@comma_str))
where not
(substring(@comma_str, n, 1)=','
and substring(@comma_str, n, 1)=substring(@comma_str, n+1, 1)))
select
string_agg(t, '') within group (order by n) CommaOnce
from
exp_str;
go
;with comma_cte(t) as (
/* odd no. of consecutive commas */
select '01008,03072,0045,r,,,,,,,,,-17' union all
/* even no. of consecutive commas */
select '01008,03072,0045,r,,,,,,,,,,-17' union all
/* mix even/odd no. of consecutive commas */
select '01008,,03072,,,0045,r,-17'union all
/* no consecutive commas */
select '01008,03072,0045,r,-17')
select *
from
comma_cte cc
cross apply
dbo.fnCommaOnce(cc.t) f;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply