SQL Query by splitting the encoded text.

  • Hi,

    I have a situation where I have bunch of 7-digit Encoded text in database like 0304182, now here

    First two characters represent Product i.e. 03

    Next two character represent Class i.e. 04 and

    Last thress characters represent SubClass i.e. 182

    Now I want to fill thress dropdowns (i.e. Product, Class, SubClass) with the distinct value from the database, where Subclass will populate on Class selection and Class will populate on Product selection.

    So can anyone suggest me the SQL query to get it to work ? Hope you understand the situation. I am using SQL server 2005.

  • steven (8/4/2009)


    Hi,

    I have a situation where I have bunch of 7-digit Encoded text in database like 0304182, now here

    First two characters represent Product i.e. 03

    Next two character represent Class i.e. 04 and

    Last thress characters represent SubClass i.e. 182

    Now I want to fill thress dropdowns (i.e. Product, Class, SubClass) with the distinct value from the database, where Subclass will populate on Class selection and Class will populate on Product selection.

    So can anyone suggest me the SQL query to get it to work ? Hope you understand the situation. I am using SQL server 2005.

    Hi Steven,

    I built this query for you, check it out, see if it does what you are looking for.

    USE tempdb

    GO

    DROP TABLE Product

    CREATE TABLE Product (

    ID INT IDENTITY ( 1 , 1 ),

    EncodedProduct VARCHAR(7))

    INSERT INTO Product

    (EncodedProduct)

    SELECT '0101001'

    UNION ALL

    SELECT '0201001'

    UNION ALL

    SELECT '0301001'

    UNION ALL

    SELECT '0301002'

    UNION ALL

    SELECT '0102001'

    UNION ALL

    SELECT '0202001'

    UNION ALL

    SELECT '0302001'

    UNION ALL

    SELECT '0303001'

    UNION ALL

    SELECT '0401001'

    UNION ALL

    SELECT '0504001'

    UNION ALL

    SELECT '0101010'

    UNION ALL

    SELECT '0102010'

    UNION ALL

    SELECT '0103010'

    UNION ALL

    SELECT '0203010'

    --This fills your PrdCode combo

    Select distinct PrdCode from (

    SELECT substring(EncodedProduct,1,2) AS PrdCode,

    Substring(EncodedProduct,3,2) AS PrdClass,

    Substring(EncodedProduct,5,3) AS PrdSubClass

    FROM Product) as Prd

    GO

    --This fills your PrdClass combo based on the PrdCode selection (@PrdCode)

    Declare @PrdCode varchar(2)

    set @PrdCode = '01'

    select distinct PrdClass from (

    SELECT substring(EncodedProduct,1,2) AS PrdCode,

    Substring(EncodedProduct,3,2) AS PrdClass,

    Substring(EncodedProduct,5,3) AS PrdSubClass

    FROM Product) as Prd

    where PrdCode = @PrdCode

    GO

    Declare @PrdCode varchar(2),

    @PrdClass varchar(2)

    SET @PrdCode = '03'

    SET @PrdClass = '01'

    select distinct PrdSubClass from (

    SELECT substring(EncodedProduct,1,2) AS PrdCode,

    Substring(EncodedProduct,3,2) AS PrdClass,

    Substring(EncodedProduct,5,3) AS PrdSubClass

    FROM Product) as Prd

    where PrdCode = @PrdCode

    AND PrdClass = @PrdClass

    Cheers,

    J-F

  • Thanks it worked for me, but I don't know how to mark your reply as Answer. can you tell me?

  • steven (8/4/2009)


    Thanks it worked for me, but I don't know how to mark your reply as Answer. can you tell me?

    I don't think you can in this forum, well, just saying it will help people who are looking for the same answer.

    Thanks for the feedback Steven, have a nice day,

    Cheers,

    J-F

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply