How to loop - 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

How to loop

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-04 : 16:06:22

So I want to do this contest on http://beyondrelational.com/blogs/tc/archive/2010/12/27/tsql-challenge-46-Remove-leading-occurrences-of-the-first-character-in-a-string.aspx

and I'm stuck. I've solved the meat of it, but I want turn it into a loop so it recognizes how many times to run. Below is my code, which is fully runnable. How can I get rid of all the repetitive when statements though?


IF OBJECT_ID('tempdb..#datamine','U') IS NOT NULL BEGIN  
	DROP TABLE #datamine 
END
GO 

CREATE TABLE #datamine ( 
String VARCHAR(MAX)) 
GO

INSERT INTO #datamine(String) 
SELECT 'X8JXab' UNION ALL
SELECT '999744499XYZ' UNION ALL
SELECT 'BBBBBBBBBBBBBBBA' UNION ALL
SELECT 'AAAAAAAAAAAAAAAA' UNION ALL
SELECT 'tsql' UNION ALL
SELECT '11234' UNION ALL
SELECT 'aaabc' UNION ALL
SELECT '000000'

--DECLARE @size INT
--SELECT @size = MAX(LEN(string)) FROM #datamine

--WHILE @size > 1
--BEGIN

select String, Results =
case 
when charindex(substring(string,2,1),string) <> 1 then right(string,len(string)-charindex(substring(string,2,1),string)+1)
when charindex(substring(string,3,1),string) <> 1 then right(string,len(string)-charindex(substring(string,3,1),string)+1)
when charindex(substring(string,4,1),string) <> 1 then right(string,len(string)-charindex(substring(string,4,1),string)+1)
when charindex(substring(string,5,1),string) <> 1 then right(string,len(string)-charindex(substring(string,5,1),string)+1)
when charindex(substring(string,6,1),string) <> 1 then right(string,len(string)-charindex(substring(string,6,1),string)+1)
when charindex(substring(string,7,1),string) <> 1 then right(string,len(string)-charindex(substring(string,7,1),string)+1)
when charindex(substring(string,8,1),string) <> 1 then right(string,len(string)-charindex(substring(string,8,1),string)+1)
when charindex(substring(string,9,1),string) <> 1 then right(string,len(string)-charindex(substring(string,9,1),string)+1)
when charindex(substring(string,10,1),string) <> 1 then right(string,len(string)-charindex(substring(string,10,1),string)+1)
when charindex(substring(string,11,1),string) <> 1 then right(string,len(string)-charindex(substring(string,11,1),string)+1)
when charindex(substring(string,12,1),string) <> 1 then right(string,len(string)-charindex(substring(string,12,1),string)+1)
when charindex(substring(string,13,1),string) <> 1 then right(string,len(string)-charindex(substring(string,13,1),string)+1)
when charindex(substring(string,14,1),string) <> 1 then right(string,len(string)-charindex(substring(string,14,1),string)+1)
when charindex(substring(string,15,1),string) <> 1 then right(string,len(string)-charindex(substring(string,15,1),string)+1)
when charindex(substring(string,16,1),string) <> 1 then right(string,len(string)-charindex(substring(string,16,1),string)+1)
else right(string,1)
end
from #datamine

Craig Greenwood

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-04 : 16:17:56

I think you missed requirement #7:
Your solution should process the data in ‘case sensitive’ manner

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-04 : 16:21:37

Lamprey...Nice catch. I think mine is case sensitive though. At least in the sense that what goes in, comes out, in terms of case. If there is something additional I can do for case sensitivity I'm all ears. That's something on the frontier of my SQL experience!

Thanks for the feedback!

I found that I did miss the special instance of '000000' should output as '0'. I fixed that by changing the "not equals" (<>) to "Greater than" (>).

Craig Greenwood

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-04 : 16:24:09

I think you also missed requirement #2: The solution should be a single query that starts with a "SELECT", "WITH" or ";WITH". A "loop" wouldn't permit that.

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-04 : 16:26:26

(sigh). You SQL folks and your details! (jk). For the sake of learnage then....lets just pretend that we want to loop through. I tried for a few hours and couldn't get anything but pretty red error messages.

Craig Greenwood

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-04 : 16:38:57

Well, if we were good SQL people, we'd tell you looping is the wrong way to do it.

There's a hint in that link about a "tally" table, you'd be well-advised to investigate that method if you're serious about this contest and/or learning SQL.

BTW, I'm pretty sure this can be solved without any loops or tally table solutions.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-04 : 16:48:37

quote:
Originally posted by craigwg

Lamprey...Nice catch. I think mine is case sensitive though. At least in the sense that what goes in, comes out, in terms of case. If there is something additional I can do for case sensitivity I'm all ears. That's something on the frontier of my SQL experience!

<snip>
Craig Greenwood

I'm not 100% of what "case sensitive" means in this case. But, try changing the AAAAAAAAAA to AAAAaAAAA I would expect the result to be aAAAA. But, maybe that's not what they mean by that (I didn't look to the forum to see if anyone asked about that or not). *shurg*

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-04 : 16:58:00

quote:
But, try changing the AAAAAAAAAA to AAAAaAAAA I would expect the result to be aAAAA.

I take the same meaning about case sensitivity, that's what I'm working towards with my solution.

craigwg
Posting Yak Master

154 Posts

Posted - 2011-01-04 : 17:03:47

Maybe I'm killing flies with cannons here but I added this for case sensitivity:

ALTER TABLE #datamine
ALTER COLUMN String VARCHAR(MAX)
COLLATE SQL_Latin1_General_CP1_CS_AS
GO

and then after the statements I run the statement again and flip it back to CI

That solves that. I'm still stuck on the multiple rows of processing though.

I looked at the tally table. I don't quite understand how having a million odd row table of numerals will assist me though.

Craig Greenwood

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-04 : 17:14:19

Take a look here: http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Then take a look at your original post. The light should come on pretty quickly.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-04 : 18:15:01

I actually came up with a solution to this one, but didn't publish it since I knew it could be better (and probably missed one of the criteria, but it passed their Basic Test). I'd like to publish it here and have the smart people here correct my thinking. Should I?

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-04 : 18:21:24

Sure, as long as you don't mind one of us taking credit for it.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-04 : 18:35:12

I'm pretty sure no one will take credit for this!

Jim

BEGIN
	IF OBJECT_ID('TC46','U') IS NOT NULL BEGIN
		DROP TABLE TC46
	END
	
	CREATE TABLE TC46 (
		String VARCHAR(MAX)	
	)
	
	INSERT INTO TC46(String)
	SELECT 'X8JXab' UNION ALL
	SELECT '999744499XYZ' UNION ALL
	SELECT 'BBBBBBBBBBBBBBBA' UNION ALL
	SELECT 'AAAAAAAAAAAAAAAA'
	;
	
	/* Insert your query below */
;with cte1 as 
(select [id] = row_number() over(order by string),string,[firstLetter]  = left(string,1),sLength = len(string)
 from tc46
)
,
cte2 as 
(select string,firstLetter,[NewString] = substring(string,1,sLength),lvl = 1,slength
 from cte1
 union all
 select string,firstLetter,substring(string,lvl+1,sLength),lvl+1,slength
 from cte2
 where lvl < slength)


SELECT 
    tc46.string
    ,isnull(t2.newstring ,left(tc46.string,1))

FROM tc46  
LEFT  JOIN

(  select * from
		(
		    select *,[row] = row_number() over(partition by string order by lvl asc) 
		    from cte2 
            where firstletter <> left (newstring,1)
		) t1
  where row = 1
 
 )t2

ON tc46.string = t2.string

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-04 : 18:37:06

Edit: argh, missed it by a few seconds, can't find the right color to match the background. Oh well.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-04 : 18:39:34

Here's what I've got so far. If you don't want to know, then don't highlight it to see it:

select string, 
Coalesce(stuff(string,1, patindex('%[^'+left(string,1)+']%' COLLATE SQL_Latin1_General_CP1_CS_AS,string)-1,''), 
Right(string,1)) Result
from tc46

I don't have the ORDER BY figured out yet. I haven't submitted this yet, feel free to steal it and submit it. Seriously.

Edit: got the ORDER BY working, highlight below if you're interested, or not if you're not:

;WITH s(String, Result) AS (SELECT String, 
COALESCE(STUFF(String, 1, PATINDEX('%[^' + LEFT(String, 1) + ']%' COLLATE SQL_Latin1_General_CP1_CS_AS, String) - 1, ''), 
LEFT(String, 1)) Result
FROM TC46)
SELECT * FROM s 
ORDER BY CAST(NULLIF(SUBSTRING(Result, 1, PATINDEX('%[^0-9.-]%', RESULT) - 1), '') AS FLOAT)

I haven't submitted it because I wasn't able to register on the site, so feel free to steal this and submit it as your own.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-04 : 18:51:18

Oy! I think I'm going to spend some time with STUFF. I don't think I've ever used it as solution to my problems but have seen it as part of a solution so many times.

Jim

Then I'll steal it.

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-04 : 19:10:20

BTW, that's brilliant. I've been hacking at SQL for many years, but have spent most of my time with "databases" that defy anything vaguely resembling a normal form and getting anything out of them makes me looks smart (one eyed man in the realm of the blind) but I want to step up my game and start realy learning SQL. The best way to do that for me is by looking at what other people did and really understanding it. I had a math Prof that told me that there's an infinite number of ways to do something wrong, you're better off focusing on the way to it right!

Thanks again.

jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-05 : 10:33:51

Thanks for the kind words. I just updated my earlier post with the full code.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-12 : 04:59:33

quote:
Originally posted by robvolk

Here's what I've got so far. If you don't want to know, then don't highlight it to see it:
select string,
Coalesce(stuff(string,1, patindex('%[^'+left(string,1)+']%' COLLATE SQL_Latin1_General_CP1_CS_AS,string)-1,''),
Right(string,1)) Result
from tc46
I don't have the ORDER BY figured out yet. I haven't submitted this yet, feel free to steal it and submit it. Seriously.

Edit: got the ORDER BY working, highlight below if you're interested, or not if you're not:
;WITH s(String, Result) AS (SELECT String,
COALESCE(STUFF(String, 1, PATINDEX('%[^' + LEFT(String, 1) + ']%' COLLATE SQL_Latin1_General_CP1_CS_AS, String) - 1, ''),
LEFT(String, 1)) Result
FROM TC46)
SELECT * FROM s
ORDER BY CAST(NULLIF(SUBSTRING(Result, 1, PATINDEX('%[^0-9.-]%', RESULT) - 1), '') AS FLOAT)
I haven't submitted it because I wasn't able to register on the site, so feel free to steal this and submit it as your own.

Very nice

.

What was the problem when you tried to register?
Did you use this for registration?
http://beyondrelational.com/user/CreateUser.aspx?ReturnUrl=

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-12 : 05:11:18

quote:
Originally posted by robvolk

Here's what I've got so far. If you don't want to know, then don't highlight it to see it:
[spoiler]select string,
Coalesce(stuff(string,1, patindex('%[^'+left(string,1)+']%' COLLATE SQL_Latin1_General_CP1_CS_AS,string)-1,''),
Right(string,1)) Result
from tc46[/spoiler]
I don't have the ORDER BY figured out yet. I haven't submitted this yet, feel free to steal it and submit it. Seriously.

Edit: got the ORDER BY working, highlight below if you're interested, or not if you're not:
[spoiler];WITH s(String, Result) AS (SELECT String,
COALESCE(STUFF(String, 1, PATINDEX('%[^' + LEFT(String, 1) + ']%' COLLATE SQL_Latin1_General_CP1_CS_AS, String) - 1, ''),
LEFT(String, 1)) Result
FROM TC46)
SELECT * FROM s
ORDER BY CAST(NULLIF(SUBSTRING(Result, 1, PATINDEX('%[^0-9.-]%', RESULT) - 1), '') AS FLOAT)[/spoiler]
I haven't submitted it because I wasn't able to register on the site, so feel free to steal this and submit it as your own.

Why dont you use a derived table?

[spoiler]
select * from
( 
select string, 
Coalesce(stuff(string,1, patindex('%[^'+left(string,1)+']%' COLLATE SQL_Latin1_General_CP1_CS_AS,string)-1,''), 
Right(string,1)) Result
from tc46
) t
ORDER BY CAST(NULLIF(SUBSTRING(Result, 1, PATINDEX('%[^0-9.-]%', RESULT) - 1), '') AS FLOAT)
[/spoiler]

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-12 : 07:37:09

I managed to register using a different email address, the first one didn't take for some reason.

I didn't use a derived table/sub-select simply because I like CTEs. I was tempted to write it without a CTE but the SQL would be pretty ugly.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources