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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Replace "X" with 0-9
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Samuelg78
Starting Member

Singapore
4 Posts

Posted - 09/29/2010 :  22:49:39  Show Profile  Reply with Quote
Hi, i will like write a stored procedure to replace "X" in a string with number 0-9. For example:

10 results
"X123": "0123", "1123", "2123", "3123", "4123", "5123",
"6123", "7123", "8123", "9123"

100 results
"X12X": "0120", "1120", "2120", "3120", "4120", ......
"0121", "1121", "2121", "3121", "4121", ......
"0122", "1122", "2122", "3122", "4122", ......


1000 Result
"XX1X": ......


Any help will be much appreciated. Thks in advance.

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 09/29/2010 :  23:13:58  Show Profile  Reply with Quote

declare @x varchar(20)='X123'
;with cte
as
(

select convert(varchar(20),replace(@x,'X',0)) as val
union all
select   convert(varchar(20),convert(varchar(20),val +1000))  as val1 from cte
where  convert(varchar(20),convert(varchar(20),val +1000))<=9123
)

select * from cte


If you know what your last number is going to be then u can put the condition in the recursive part of the CTE & add by 1000 the way I did it.
Maybe someone else comes with much more feasible solution


PBUH

Go to Top of Page

Samuelg78
Starting Member

Singapore
4 Posts

Posted - 09/30/2010 :  00:22:43  Show Profile  Reply with Quote
Your solution only work for X123. But there may be some other combination like '1X23', '123X'. And also for more than one 'X' scenario, 'XX23', 'X9XX', etc.
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 09/30/2010 :  00:26:31  Show Profile  Reply with Quote
What should be o/p for '1X23'& '123X'?

PBUH

Go to Top of Page

Samuelg78
Starting Member

Singapore
4 Posts

Posted - 09/30/2010 :  00:56:05  Show Profile  Reply with Quote
X will represent number 0-9

'1X23' : 1023, 1123, 1223, 1323, 1423, 1523, 1623, ......
'123X' : 1230, 1231, 1232, 1233, 1234, 1235, 1236, ......

I already got the sp working for string contain one "X".
I need help in more than one "X".

<code>
CREATE FUNCTION [dbo].[GRollPermutations]
(
@Number varchar(5)
)
RETURNS @Temp Table
(
Number varchar(4)
)
AS

BEGIN
Declare @Count tinyint
SET @Count = 1
Declare @Char char(1)

WHILE @Count <= LEN(@Number)
Begin
Set @Char = SUBSTRING(@Number, @Count, 1)

If UPPER(@Char) = 'X'
Begin
Declare @j tinyint
Set @j = 0
While @j < 10
Begin
Insert @Temp Select dbo.ReplaceFirst(@Number, 'X', @j)

SET @j = @j + 1
End

Break
End




SET @Count = @Count + 1
End

RETURN
END
</code>
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000