Link to home
Start Free TrialLog in
Avatar of mburk1968
mburk1968Flag for United States of America

asked on

SQL Syntax help for passing multi-value parameter to a substring up until the first space

I have a table of data with a column named style. the data in that column is formatted in a way that it includes style, label and size.
Style
1177044 33C XL
2177003 33C 06J04
3173327E 33C 0610
Etc....

I have an SSRS report that I want to allow the end user to be able to search by multiple styles by manually entering them into the Parameter field.

I have created the following query which works perfectly if I only enter one style. However If I search by multiple it returns nothing. I'm using this piece of code ( LEFT(style, CHARINDEX(' ', style) - 1) ) to grab just the style number up to the first space.

SELECT  style ,
        style_name ,
        group_code ,
        color_name ,
        color_code ,
        size_name ,
        size_code ,
        UPC ,
        pack_breakdown ,
        GTIN ,
        length ,
        width ,
        height ,
        weight ,
        cube ,
        pcs_per_ctn ,
        pcs_per_innr ,
        innr_per_ctn ,
        pcs_per_poly ,
        poly_per_ctn ,
        pack ,
        UOM ,
        dimension ,
        lbl_code ,
        CASE WHEN ( group_code = 'KLL' ) THEN ( '49880-00' )
             ELSE ( '84260-05' )
        END AS NMFC ,
        CASE WHEN ( group_code = 'KLL' ) THEN ( '100' )
             ELSE ( '125' )
        END AS Class ,
        date_added AS Date
FROM    KLL_Cust.dbo.Phoenix832
WHERE   ( group_code = @Division )
        AND ( ( @Style ) IN ( LEFT(style, CHARINDEX(' ', style) - 1) ) )
ORDER BY group_code;

Open in new window


What do I need to do in order to pull back multiple styles?
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mburk1968

ASKER

Thank you! This was a huge help.