?
Solved

SQL Expression

Posted on 2013-01-09
4
Medium Priority
?
176 Views
Last Modified: 2013-01-09
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.
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 664 total points
ID: 38759039
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 668 total points
ID: 38759048
Try this:

SELECT ITEM_ID, DESCRIPTION FROM INVENTORY
WHERE ITEM_ID LIKE ('[A-Za-z][A-Za-z][A-Za-z]%')
0
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 668 total points
ID: 38759252
I think
WHERE ITEM_ID LIKE ('[A-Za-z][A-Za-z][A-Za-z]%[0-9]')
will suffice
0
 

Author Closing Comment

by:holemania
ID: 38759690
Thanks.
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question