Prefix - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Prefix

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-09-30 : 08:49:36

Dear All

I have policyid field and I try to select all policyid which has ‘JUU’ as prefix.

Select policyid from policy where policyid like ‘%JUU%’

Result
JUU1534322
JUU1596832
IPW1596821, JUU1506857
JUU15965452, HOI1596822

It also pulls other policyid which contains different prefix(case:two different policyid merged in same filed). How to eliminate that ids

Expected result
JUU1534322
JUU1596832
JUU1506857
JUU15965452

Any help will be highly appreciated,

Thanks
SG

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-09-30 : 08:57:30

Select policyid from policy where policyid like ‘JUU%’
and policyid not like '%,%'

If this is supposed to be an id field, why do you have more than one value per row?!?

memorykills
Starting Member

18 Posts

Posted - 2011-09-30 : 13:03:04

select policyID
from policy
where policyid like 'JUU%' and policyid not like '%,%'
UNION ALL
select RIGHT(policyID, LEN(policyID) - patindex('%,%',policyID)-1)
from policy
where policyid like '%, JUU%' and policyid like '%,%'
UNION ALL
select LEFT(policyID, patindex('%,%',policyID)-1)
from policy
where policyid like 'JUU%,%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources