Replace "X" with 0-9

Question

Post reply

Replace "X" with 0-9

samuelg78

Old Hand

Points: 338
More actions
September 29, 2010 at 9:01 pm

#98909

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's one example using a CTE Tally Table:

with Tally as (

SELECT TOP 11000 --only need ten digits for this job, though

ROW_NUMBER() OVER (ORDER BY name) -1 AS N

FROM Master.dbo.SysColumns sc1

)

SELECT TOP 10 REPLACE('X123','X',CONVERT(varchar,Tally.N))

from Tally

for your "X12X" example, you need to define the rules...two X's woluld both get replaced with the same number in my example...your's seems to increment, but not in a pattern i could identify.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

UMG Developer SSChampion Points: 13482 More actions · Answer 2

Lowell (9/29/2010)
for your "X12X" example, you need to define the rules...two X's woluld both get replaced with the same number in my example...your's seems to increment, but not in a pattern i could identify.

I think he did by saying that two Xs should result in 100 numbers, and three Xs should result in 1,000 numbers. Essentially he wants each X treated separately returning all combinations of all the Xs. So for each X you multiple the number of results by 10.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 3

This may be a little clunky, but it works. Downside is that it is limited to varchars of only 4 chars/digits. It also uses 4 punctuation characters that may interfere with processing if your original string/pattern has them in it. But, assuming that the incoming value has only the characters 0,1,2,3,4,5,6,7,8,9 and X in it, it will work.

Edit: Note that I stole part of Lowell's original answer -- he gets credit for that!

declare @ChkStr varchar(10)

set @ChkStr = '10XX'

;with Tally as (

SELECT TOP 10 --only need ten digits for this job

ROW_NUMBER() OVER (ORDER BY name) -1 AS N

FROM Master.dbo.SysColumns sc1

),

PASS1 as

(SELECT DISTINCT CASE WHEN CHARINDEX('X', @ChkStr) > 0

THEN STUFF(@ChkStr, CHARINDEX('X', @ChkStr),1,'%')

ELSE @ChkStr END as ChkStr),

PASS2 as

(SELECT DISTINCT CASE WHEN CHARINDEX('X', PASS1.ChkStr) > 0

THEN STUFF(PASS1.ChkStr, CHARINDEX('X', PASS1.ChkStr),1,'^')

ELSE PASS1.ChkStr END as ChkStr FROM PASS1),

PASS3 as

(SELECT DISTINCT CASE WHEN CHARINDEX('X', PASS2.ChkStr) > 0

THEN STUFF(PASS2.ChkStr, CHARINDEX('X', PASS2.ChkStr),1,'&')

ELSE PASS2.ChkStr END as ChkStr FROM PASS2),

PASS4 as

(SELECT DISTINCT CASE WHEN CHARINDEX('X', PASS3.ChkStr) > 0

THEN STUFF(ChkStr, CHARINDEX('X', PASS3.ChkStr),1,'*')

ELSE PASS3.ChkStr END as ChkStr FROM PASS3),

PASS5 AS

(SELECT DISTINCT REPLACE(P.ChkStr,'%',CONVERT(varchar,T.N)) AS ChkStr

FROM TALLY T, PASS4 P),

PASS6 AS

(SELECT DISTINCT REPLACE(P.ChkStr,'^',CONVERT(varchar,T.N)) AS ChkStr

FROM TALLY T, PASS5 P),

PASS7 AS

(SELECT DISTINCT REPLACE(P.ChkStr,'&',CONVERT(varchar,T.N)) AS ChkStr

FROM TALLY T, PASS6 P)

SELECT DISTINCT REPLACE(P.ChkStr,'*',CONVERT(varchar,T.N)) AS ChkStr

from Tally T, PASS7 P

order by ChkStr

Rob Schripsema
Propack, Inc.

samuelg78 Old Hand Points: 338 More actions · Answer 4

Thks. Thats working. But why you need to use like "%", "^", "*" in the query? And its also work i i pass in the punctuation.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 5

samuelg78 (9/30/2010)
Thks. Thats working. But why you need to use like "%", "^", "*" in the query? And its also work i i pass in the punctuation.

I simply used the odd punctuation to separate out multiple instances of the 'X' character. As Lowell pointed out, otherwise the REPLACE function will replace ALL occurrences of 'X' with the same digit -- and that's not the behavior you wanted. I made 4 passes of each of the two steps to accommodate the fact that you had 4 characters in your example target string, and any one or more could be 'X'. CHARINDEX will find just the first occurrence of the given character, and STUFF can be made to replace just a single character, so you could effectively deal with just one 'X' at a time.

I'm sure there are other ways to do this (I can think of at least one right now), but this was what came to me first, and it appeared to provide one way to accomplish what you needed. I was trying to avoid any explicit loops in the code.

Rob Schripsema
Propack, Inc.