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 Friday, December 7, 2007 1:05 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:11 AM
Points: 83, Visits: 502
I have a character column which will have varying data, and potentially varying lengths. It needs to be standardized to 4 characters, adding in zeroes either to the front or middle, depending if the value starts with a letter(s) or not.

A012
A12
12
1
AB12
AB2

etc.

Then I would be looking at evaluating them to update them all to
A012
A012
0012
0001
AB12
AB02

I know how I want to script the actual update, but first I need to know how many combinations there are in the data, so I know what to accomodate for.
Is there a simple way to poll this information to tell me how many combinations there are? Eg. something that would come back and tell me if each character is a letter or a number and return me the combo like:

letter-num-num-num
letter-num-num
num-num
etc.

I know there's a hard way to do this, but I've got to prep the information to give to some one else to actually gather the answer for me, so I want to make it as easy as possible.
Post #430846
Posted Friday, December 7, 2007 1:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:59 AM
Points: 7,115, Visits: 14,979
You can do it a few ways. Personally I'm a fan of using .NET regular expressions to do just that, but that involves allowing CLR integration, which some are leery to allow.
Failing that - you can use something like patindex to help figure it out. You'll have to run 8 separate searches, or columns.

Patindex allows you to do something like

select count(*),
sum(case when patindex('%[a-zA-Z][0-9][0-9][0-9]%',myfield)=1 then 1 else 0 end) as A000,
sum(case when patindex('%[a-zA-Z][a-zA-Z][0-9][0-9]%',myfield)=1 then 1 else 0 end) as AA00,

etc...
from
table1





----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #430850
Posted Saturday, December 8, 2007 10:59 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
You'll have to run 8 separate searches, or columns


Or... we can cheat like hell :P

--===== This just creates demonstrable test data and is NOT part of the solution
SET NOCOUNT ON
DECLARE @TestData TABLE(String VARCHAR(10))
INSERT INTO @TestData (String)
SELECT 'A012' UNION ALL
SELECT 'A12' UNION ALL
SELECT '12' UNION ALL
SELECT '1' UNION ALL
SELECT 'AB12' UNION ALL
SELECT 'AB2'

--===== This is the solution for the known constraints on the data as posted
SELECT Original = String,
Modified = CASE
WHEN d.LastLetter = 0 THEN RIGHT('0000'+d.String,4)
WHEN d.LastLetter = 1 THEN LEFT(d.String,d.LastLetter)
+ RIGHT('0000'+SUBSTRING(d.String,d.FirstDigit,4),3)
WHEN d.LastLetter = 2 THEN LEFT(d.String,d.LastLetter)
+ RIGHT('0000'+SUBSTRING(d.String,d.FirstDigit,4),2)
END
FROM (--==== Derived table "d" finds the interface between letters/digits
SELECT String,
LastLetter = PATINDEX('%[a-z][0-9]%',String),
FirstDigit = PATINDEX('%[0-9]%',String)
FROM @TestData
)d

RESULTS:
=======
Original Modified
---------- -------------
A012 A012
A12 A012
12 0012
1 0001
AB12 AB12
AB2 AB02





--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 #430970
Posted Saturday, December 8, 2007 11:14 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
...or, perhaps, something a bit more "auto-magic"... :D

--===== This just creates demonstrable test data and is NOT part of the solution
SET NOCOUNT ON
DECLARE @TestData TABLE(String VARCHAR(10))
INSERT INTO @TestData (String)
SELECT 'A012' UNION ALL
SELECT 'A12' UNION ALL
SELECT '12' UNION ALL
SELECT '1' UNION ALL
SELECT 'AB12' UNION ALL
SELECT 'AB2'


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

RESULTS:
========
Original Modified
---------- ------------------
A012 A012
A12 A012
12 0012
1 0001
AB12 AB12
AB2 AB02



--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 #430972
Posted Saturday, December 8, 2007 1:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,826, Visits: 3,866
:)Or in a simple query:
SELECT	string Original
,CASE WHEN ISNUMERIC(string) = 0
THEN LEFT(string, PATINDEX('%[0-9]%',string)-1)
ELSE '' END -- Characters first
+ REPLICATE('0', 4-LEN(string)) -- Stuff with zeros
+ SUBSTRING(string, PATINDEX('%[0-9]%',string), 4) -- Add numeric remainder
AS Modified
FROM @TestData



Best Regards,
Chris Büttner
Post #430983
Posted Saturday, December 8, 2007 5:15 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
Drat... both of ours come up with a little problem when we have data like this...

--===== This just creates demonstrable test data and is NOT part of the solution
SET NOCOUNT ON
DECLARE @TestData TABLE(String VARCHAR(10))
INSERT INTO @TestData (String)
SELECT 'A012' UNION ALL
SELECT 'A12' UNION ALL
SELECT '12' UNION ALL
SELECT '1' UNION ALL
SELECT 'AB12' UNION ALL
SELECT 'AB2' UNION ALL
SELECT 'A' UNION ALL
SELECT ''


--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 #431005
Posted Saturday, December 8, 2007 5:55 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
This takes care of the single letter and blank situations...
--===== This is the solution for the known constraints on the data as posted
DECLARE @MaxWidth TINYINT
SET @MaxWidth = (SELECT MAX(LEN(String)) FROM @TestData)
SELECT Original = String,
Modified = LEFT(d.String,d.LastLetter)
+ RIGHT(
REPLICATE('0',@MaxWidth)
+ SUBSTRING(d.String,d.LastLetter+1,@MaxWidth)
, @MaxWidth-d.LastLetter)
FROM (--==== Derived table "d" finds the interface between letters/digits
SELECT String,
LastLetter = PATINDEX('%[a-z][0-9]%',String+'0')
FROM @TestData
)d

If ya really gotta have it as a single query, replace @MaxWidth with 4 and replace d.LastLetter with the formula for LastLetter.


--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 #431007
Posted Monday, December 17, 2007 8:04 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:11 AM
Points: 83, Visits: 502
Interesting, definitely some syntax in there I have to learn..

Jeff, your "auto magic" one came the closest but I got some extra zeros I haven't quite figured yet -

Original Modified
-------------------
A14 A00014
B2 B0002
OBT 0000OBT
YH3 YH0003
XX 0000XX

So - too many zeroes, the whole string must be 4. And if there was no digit found it puts the zeros at the beginning of the string rather than after the letters.

Your query accomodating for blanks didn't give me any modification, similarly the earlier non-auto magic query didn't produce a modification.

And the other query offered, the simple one -

SELECT String Original
,CASE WHEN ISNUMERIC(Page) = 0
THEN LEFT(Page, PATINDEX('%[0-9]%',String)-1)
ELSE '' END -- Characters first
+ REPLICATE('0', 4-LEN(Page)) -- Stuff with zeros
+ SUBSTRING(String, PATINDEX('%[0-9]%',String), 4) -- Add numeric remainder
AS Modified
FROM @TestData


This one gives me some results for short page lengths but none of the more complex ones are returned and I got an error "invalid length parameter passed to the substring function."

The query would only be evaluating records where the field has content (so no worries about blanks) and where the content is less than 4 characters. If it has 4 or more it will be ignored in the update.
Post #433881
Posted Monday, December 17, 2007 8:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,826, Visits: 3,866
The following query should also take care of values without digits:

SELECT	string Original		
,CASE WHEN PATINDEX('%[0-9]%',string) > 0
THEN LEFT(string, PATINDEX('%[0-9]%',string)-1)
ELSE RTRIM(string) END -- Characters first
+ REPLICATE('0', 4-LEN(string)) -- Stuff with zeros
+ CASE WHEN PATINDEX('%[0-9]%',string) > 0
THEN SUBSTRING(string, PATINDEX('%[0-9]%',string), 4)
ELSE '' END -- Numeric Trailer
AS Modified
FROM @TestData



Best Regards,
Chris Büttner
Post #433908
Posted Monday, December 17, 2007 11:04 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
Jeff, your "auto magic" one came the closest but I got some extra zeros I haven't quite figured yet -

Original Modified
-------------------
A14 A00014
B2 B0002
OBT 0000OBT
YH3 YH0003
XX 0000XX

So - too many zeroes, the whole string must be 4. And if there was no digit found it puts the zeros at the beginning of the string rather than after the letters.



Yes... in my first attempt, that's what happens... did you try the second attempt? For your convenience, here it is again...

--===== This just creates demonstrable test data and is NOT part of the solution
SET NOCOUNT ON
DECLARE @TestData TABLE(String VARCHAR(10))
INSERT INTO @TestData (String)
SELECT 'A012' UNION ALL
SELECT 'A12' UNION ALL
SELECT '12' UNION ALL
SELECT '1' UNION ALL
SELECT 'AB12' UNION ALL
SELECT 'AB2' UNION ALL
SELECT 'A' UNION ALL
SELECT '' UNION ALL
SELECT 'A14' UNION ALL
SELECT 'B2' UNION ALL
SELECT 'OBT' UNION ALL
SELECT 'YH3' UNION ALL
SELECT 'XX'


--===== This is the solution for the known constraints on the data as posted
DECLARE @MaxWidth TINYINT
SET @MaxWidth = (SELECT MAX(LEN(String)) FROM @TestData)
SELECT Original = String,
Modified = LEFT(d.String,d.LastLetter)
+ RIGHT(
REPLICATE('0',@MaxWidth)
+ SUBSTRING(d.String,d.LastLetter+1,@MaxWidth)
, @MaxWidth-d.LastLetter)
FROM (--==== Derived table "d" finds the interface between letters/digits
SELECT String,
LastLetter = PATINDEX('%[a-z][0-9]%',String+'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 #433982
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse