[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Expression

Posted on 2013-01-09
4
Medium Priority
?
177 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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.…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

650 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