April 2, 2012 at 7:15 pm
Hi Forumer's,
Kindly please help me pull out all ItemID with
'-R', '-IR', '-S' and itemid's that start with LGE,SAM,HTC with corresponding length of 7 chr.
Based on my script, there are some itemid's that should no be included but they are still in the result.
Here is my Query.
Create table #table1
(ItemID nvarchar(35))
Insert into #table1 (ItemID) values ('HTC1007')
Insert into #table1 (ItemID) values ('HTC1007-IR')
Insert into #table1 (ItemID) values ('HTC1007-R')
Insert into #table1 (ItemID) values ('HTC1007-S')
Insert into #table1 (ItemID) values ('LGE2985-R')
Insert into #table1 (ItemID) values ('LGE2985')
Insert into #table1 (ItemID) values ('LGE2986')
Insert into #table1 (ItemID) values ('SAM1217')
Insert into #table1 (ItemID) values ('SAM1217-R')
Insert into #table1 (ItemID) values ('SAM1217-S')
Insert into #table1 (ItemID) values ('SAM-H0159-BER')
Insert into #table1 (ItemID) values ('A870-PK')
Insert into #table1 (ItemID) values ('AUD1351')
Insert into #table1 (ItemID) values ('REPZ6TV')
Result:
ItemID
-------
HTC1007
HTC1007-IR
HTC1007-R
LGE2985-R
LGE2985
LGE2986
SAM1217-R
SAM1217-S
SAM-H0159-BER
SELECT
inv.ITEMID
,CASE WHEN RIGHT(inv.ITEMID, 3) = 'IR' THEN 'IR'
WHEN RIGHT(inv.ITEMID, 2) = '-R' THEN '-R'
WHEN RIGHT(inv.ITEMID, 2) = '-S' THEN '-S'
WHEN LEFT(inv.ITEMID,3)='HTC' OR LEFT(inv.ITEMID,3)='SAM'
OR LEFT(inv.ITEMID,3)='LGE'
and RIGHT(inv.ITEMID, 2) <> 'IR'
or RIGHT(inv.ITEMID, 2) <> '-R'
or RIGHT(inv.ITEMID, 2) = '-S' THEN 'OEM'
ELSE '' END AS Remarks
FROM #table1 AS inv
Where RIGHT(inv.ITEMID,3) ='IR'
or RIGHT(inv.ITEMID,2)='-R'
or RIGHT(inv.ITEMID,2)='-S'
or inv.ITEMID = LEFT(inv.itemid,7)
and RIGHT(inv.ITEMID, 2) <> 'IR' and RIGHT(inv.ITEMID, 2) <> '-R' or RIGHT(inv.ITEMID, 2) = '-S'
Thank you in Advance.
April 3, 2012 at 1:22 am
Check if this helps.
select * from #table1
where (ItemID like 'LGE%' or ItemID like 'SAM%' OR ItemID like 'HTC%')
and (ItemID like '%-S' or ItemID like '%-R' or ItemID like '%-IR')
and CHARINDEX('-', ItemID) = 8
April 3, 2012 at 6:51 pm
Thanks for the reply.
Only with -R,-IR, -S are obtain by the query.
but the 7 character string that start from (HTC,LGE,SAM) are missing from the result set.
Derived result from the Query.
HTC1007-IR
HTC1007-R
HTC1007-S
LGE2985-R
SAM1217-R
SAM1217-S
Missing:
HTC1007
LGE2985
LGE2986
SAM1217
April 4, 2012 at 2:35 am
Hi
Just a slight amendment to the query posted previously should do the trick for you
SELECT *
FROM #table1
WHERE
(ItemID LIKE 'LGE%' OR ItemID LIKE 'SAM%' OR ItemID LIKE 'HTC%')
OR (ItemID LIKE '%-S' OR ItemID LIKE '%-R' OR ItemID LIKE '%-IR')
AND CHARINDEX('-', ItemID) = 8
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 5, 2012 at 3:36 am
Seems I missed that condition. Thanks for the amendmend Andy.
Cheers. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply