SQL Clone
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
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 535
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.
Matt Miller (4)
Matt Miller (4)
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20805 Visits: 18903
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153231 Visits: 41766
You'll have to run 8 separate searches, or columns


Or... we can cheat like hell Tongue

--===== 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153231 Visits: 41766
...or, perhaps, something a bit more "auto-magic"... BigGrin

--===== 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Christian Buettner-167247
Christian Buettner-167247
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4557 Visits: 3889
SmileOr 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153231 Visits: 41766
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153231 Visits: 41766
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mindy Hreczuck
Mindy Hreczuck
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 535
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.
Christian Buettner-167247
Christian Buettner-167247
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4557 Visits: 3889
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153231 Visits: 41766
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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