Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to loop through values and strip 1 character at a time from the values and display the result.

So if I have a table with values:

ID
___
34679
13390
89906

I want the result to look like this

Id
----
4679
679
79
9
3390
390
90
0
9906
906
06
6
share|improve this question

2 Answers 2

up vote 10 down vote accepted

Please don't use loops for things like this (I'd also reserve recursive CTEs for scenarios where you have much less control over things, like hierarchies). Loops are bad in SQL; SQL is optimized to work in sets.

DECLARE @foo TABLE(ID INT);

INSERT @foo VALUES(34679),(13390),(89906);

;WITH x AS 
(
  SELECT TOP (2048) n = ROW_NUMBER() OVER (ORDER BY Number)
  FROM master.dbo.spt_values ORDER BY Number
)
SELECT RIGHT(f.ID, x.n) FROM x
INNER JOIN @foo AS f
ON x.n < LEN(f.ID);

Results:

9
79
679
4679
0
90
390
3390
6
06
906
9906
share|improve this answer
    
Thanks allot for your help. this is exactly what I was trying to achieve. –  Kashif Qureshi May 2 at 18:58
CREATE PROC udploop (@num varchar(10))
AS
       BEGIN 
             DECLARE @len int; 
             SET @len = LEN(@num); 
             WHILE (@len > 1)
                   BEGIN    
                         SELECT
                            @num = RIGHT(@num, @len - 1); 
                         PRINT @num;
                         SET @len = @len; 
                   END 
       END

EXEC:

EXEC udploop 34679 
EXEC udploop 13390 
EXEC udploop 89906

RESULT:

4679 
679 
79 
9 
3390 
390 
90 
0 
9906 
906 
06 
6
share|improve this answer
1  
So how do you propose you do this, when there are many rows in the table? Call the procedure - in a loop - for every row? Now you need a query to pull all of those values, and then creates the code to call the stored procedure for each one. So you have a loop for every row in the table that calls a procedure that itself runs a loop for every character in every value. This is definitely not an efficient way to solve this problem. –  Aaron Bertrand May 2 at 14:34
    
thanks. But, I agree with Aaron. This is not what i wanted. my table have more than 300k values. so this will not work. –  Kashif Qureshi May 2 at 19:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.