SQL Query Partial Values

Posted on 2009-04-30
Last Modified: 2012-05-06
How would I query a partial value from the database?  

TireType field values:

Hi-Performance A/S - SKU=1511000
Touring All-Season - SKU=413165507
Ultra High Performance - SKU=50059M
Ultra High Performance - SKU=500XYZ

Example query:

Select tireType
from myTires
where LEFT(myTires.TireType, PATINDEX('%- SKU%', myTires.TireType) IN ('Hi-Performance A/S','Ultra High Performance ')

I would like the results to return:

Hi-Performance A/S - SKU=1511000
Ultra High Performance - SKU=50059M
Ultra High Performance - SKU=500XYZ

Please advise on how to correct the query to return desired results.

Thank you!

Question by:splendorx
    LVL 25

    Accepted Solution

    You are just missing an extra parenthesis in your where clause.. Try -
    Select tireType
    from myTires
    WHERE	PATINDEX('%- SKU%', myTires.TireType) > 0
    AND	LEFT(myTires.TireType, PATINDEX('%- SKU%', myTires.TireType) - 1) IN ('Hi-Performance A/S','Ultra High Performance ')

    Open in new window

    LVL 1

    Author Closing Comment

    Thanks for your help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    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.…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now