Solved

SQL Expression

Posted on 2013-01-09
4
175 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 166 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 167 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 167 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

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
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.

689 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