Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query by splitting the encoded text. Expand / Collapse
Author
Message
Posted Tuesday, August 4, 2009 12:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 31, 2009 7:47 AM
Points: 15, Visits: 95
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.
Post #765065
Posted Tuesday, August 4, 2009 1:32 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 707, Visits: 2,706
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
Post #765094
Posted Tuesday, August 4, 2009 1:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 31, 2009 7:47 AM
Points: 15, Visits: 95
Thanks it worked for me, but I don't know how to mark your reply as Answer. can you tell me?
Post #765109
Posted Tuesday, August 4, 2009 1:59 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 707, Visits: 2,706
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
Post #765115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse