Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identify letter vs. number


Identify letter vs. number

Author
Message
Mindy Hreczuck
Mindy Hreczuck
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 531
Yes I did try that one, and it didn't appear to make a modification, but I ran it again, here's a snippet of the results:
Original Modified
-----------------------------------
B10 B0
B101 B01
B11 B1
B12 B2

Here's a test sample of what my data looks like, this should get you the same results as I see.

DECLARE @TestData TABLE(page char(32))
insert @testdata (page)
select 'G18' union all
select 'G2' union all
select 'D99' union all
select 'A08' union all
select '16' union all
select 'ENT' union all
select 'M183' union all
select 'PAGE1'


DECLARE @MaxWidth TINYINT
SET @MaxWidth = (SELECT MAX(LEN(Page)) FROM Arch1)
SELECT Original = Page,
Modified = LEFT(d.Page,d.LastLetter)
+ RIGHT(
REPLICATE('0',@MaxWidth)
+ SUBSTRING(d.Page,d.LastLetter+1,@MaxWidth)
, @MaxWidth-d.LastLetter)
FROM (--==== Derived table "d" finds the interface between letters/digits
SELECT Page,
LastLetter = PATINDEX('%[a-z][0-9]%',Page+'0')
FROM @testdata
)d
group by page, d.lastletter
order by page
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45022 Visits: 39887
Mindy, the problem is that you have the data stored in a CHAR(32)... trailing blanks come into play there. Either change the column to a VARCHAR(32) or modify my code as follows...

DECLARE @TestData TABLE(page char(32))
insert @testdata (page)
select 'G18' union all
select 'G2' union all
select 'D99' union all
select 'A08' union all
select '16' union all
select 'ENT' union all
select 'M183' union all
select 'PAGE1'

--===== This is the solution for the known constraints on the data as posted
DECLARE @MaxWidth TINYINT
SET @MaxWidth = (SELECT MAX(LEN(page)) FROM @TestData)
SELECT Original = page,
Modified = LEFT(d.page,d.LastLetter)
+ RIGHT(
REPLICATE('0',@MaxWidth)
+ SUBSTRING(d.page,d.LastLetter+1,@MaxWidth)
, @MaxWidth-d.LastLetter)
FROM (--==== Derived table "d" finds the interface between letters/digits
SELECT Page = RTRIM(page),
LastLetter = PATINDEX('%[a-z][0-9]%',RTRIM(page)+'0')
FROM @TestData
)d

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mindy Hreczuck
Mindy Hreczuck
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 531
Aaah, well I don't have control of the schema but that makes sense. Thanks! Smile
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45022 Visits: 39887
Like I said... use the code I just posted... it takes the CHAR(32) into account...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mindy Hreczuck
Mindy Hreczuck
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 531
It's still putting in an additional zero, but I'll figure that out. The syntax is a little over my head so this is a good project for me.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45022 Visits: 39887
The addition zero is coming from the fact that it's automatic code... it figures out that the "Page1" entry is 5 characters long and uses that asthe length for all the others.

If you want to force 4 characters even though 5 or more may be present, then the following will work...
DECLARE @TestData TABLE(page char(32))
insert @testdata (page)
select 'G18' union all
select 'G2' union all
select 'D99' union all
select 'A08' union all
select '16' union all
select 'ENT' union all
select 'M183' union all
select 'PAGE1'

--===== This is the solution for the known constraints on the data as posted
DECLARE @MaxWidth TINYINT
SET @MaxWidth = 4
SELECT Original = page,
Modified = LEFT(d.page,d.LastLetter)
+ RIGHT(
REPLICATE('0',@MaxWidth)
+ SUBSTRING(d.page,d.LastLetter+1,@MaxWidth)
, @MaxWidth-d.LastLetter)
FROM (--==== Derived table "d" finds the interface between letters/digits
SELECT Page = RTRIM(page),
LastLetter = PATINDEX('%[a-z][0-9]%',RTRIM(page)+'0')
FROM @TestData
)d



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mindy Hreczuck
Mindy Hreczuck
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 531
Yep that does it. Thanks so much.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search