Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Identify letter vs. number Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2007 9:49 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:11 AM
Points: 83, Visits: 505
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
Post #434407
Posted Tuesday, December 18, 2007 10:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 35,259, Visits: 31,740
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #434437
Posted Tuesday, December 18, 2007 2:24 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:11 AM
Points: 83, Visits: 505
Aaah, well I don't have control of the schema but that makes sense. Thanks! :)
Post #434544
Posted Wednesday, December 19, 2007 7:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 35,259, Visits: 31,740
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #434753
Posted Wednesday, December 19, 2007 7:39 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:11 AM
Points: 83, Visits: 505
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.
Post #434765
Posted Wednesday, December 19, 2007 7:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 35,259, Visits: 31,740
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #434771
Posted Wednesday, December 19, 2007 12:03 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:11 AM
Points: 83, Visits: 505
Yep that does it. Thanks so much.
Post #434902
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse