Replace "X" with 0-9 - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Replace "X" with 0-9

Author

Topic

Samuelg78
Starting Member

4 Posts

Posted - 2010-09-29 : 22:49:39

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

2937 Posts

Posted - 2010-09-29 : 23:13:58

[code]
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
[/code]

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

Samuelg78
Starting Member

4 Posts

Posted - 2010-09-30 : 00:22:43

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.

Sachin.Nand

2937 Posts

Posted - 2010-09-30 : 00:26:31

What should be o/p for '1X23'& '123X'?

PBUH

Samuelg78
Starting Member

4 Posts

Posted - 2010-09-30 : 00:56:05

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>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources