SQL Expression

Hello experts,

I have a need to query some data from our Item master table.  However, the item_id consists of mixed numer, alpha, and alphanumeric.

I only wants to pull the alphanumeric numbers.  The first 3 or more length of the item_id needs to be alpha follow some numbers.

Example:

122929099Klkls  <--don't pull
TTED992929        <--This is the one I want
2939392090        <--don't pull

With the above how can I construct my query to pull only alphanumeric item_id and the first 3 characters are more needs to be alpha?

SELECT ITEM_ID, DESCRIPTION FROM INVENTORY
WHERE SUBSTRING(ITEM_ID, 1, 3) LIKE ('[A-Za-z]%')

Can't get the above to look at least the first 3 characters to make sure it's Alpha.  It's pulling A12322323 into my report.  I need to have at least 3 letters or more followed by numbers.
holemaniaAsked:
Who is Participating?
 
RehanYousafConnect With a Mentor Commented:
I think
WHERE ITEM_ID LIKE ('[A-Za-z][A-Za-z][A-Za-z]%[0-9]')
will suffice
0
 
CluskittConnect With a Mentor Commented:
SELECT ITEM_ID, DESCRIPTION FROM INVENTORY
WHERE SUBSTRING(ITEM_ID, 1, 3) LIKE ('[A-Za-z][A-Za-z][A-Za-z]%[0-9][0-9][0-9]')

first 3 being letters and last 3 being numbers
0
 
mbizupConnect With a Mentor Commented:
Try this:

SELECT ITEM_ID, DESCRIPTION FROM INVENTORY
WHERE ITEM_ID LIKE ('[A-Za-z][A-Za-z][A-Za-z]%')
0
 
holemaniaAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.