April 14, 2009 at 10:32 pm
hayzer (4/14/2009)
try this code...suppose you have the table 'tblNames' with a column 'name'
select left(name,charindex('-', name)-1)
from tblNames union all
select left(name,charindex('/', name)-1)
from tblNames
havent tried it... i'll try this code at home...
but i think it should work..
br
jon
select left(name,charindex('-', name)-1)
from tblNames
where name like '%-%'
union all
select left(name,charindex('/', name)-1)
from tblNames
where name like '%/%'
that will do the trick! 🙂
April 14, 2009 at 10:54 pm
hayzer (4/14/2009)
hayzer (4/14/2009)
try this code...suppose you have the table 'tblNames' with a column 'name'
select left(name,charindex('-', name)-1)
from tblNames union all
select left(name,charindex('/', name)-1)
from tblNames
havent tried it... i'll try this code at home...
but i think it should work..
br
jon
select left(name,charindex('-', name)-1)
from tblNames
where name like '%-%'
union all
select left(name,charindex('/', name)-1)
from tblNames
where name like '%/%'
that will do the trick! 🙂
Hi hayzer,
By your statement
declare @abc table
(
name1 varchar(20)
)
insert into @abc values('JOHN-1234')
insert into @abc values ('JOHN/1234')
insert into @abc values ('JOHNX/1234-2')
insert into @abc values ('JOHNY/1234/2')
--
select left(name1,charindex('-', name1)-1)
from @abc
where name1 like '%-%'
union all
select left(name1,charindex('/', name1)-1)
from @abc
where name1 like '%/%'
--
RESULT
JOHN
JOHNX/1234
JOHN
JOHNX
JOHNY
Because of these results only, flo wrote all possible.
ARUN SAS
April 15, 2009 at 2:46 am
arun.sas (4/14/2009)
hayzer (4/14/2009)
hayzer (4/14/2009)
try this code...suppose you have the table 'tblNames' with a column 'name'
select left(name,charindex('-', name)-1)
from tblNames union all
select left(name,charindex('/', name)-1)
from tblNames
havent tried it... i'll try this code at home...
but i think it should work..
br
jon
select left(name,charindex('-', name)-1)
from tblNames
where name like '%-%'
union all
select left(name,charindex('/', name)-1)
from tblNames
where name like '%/%'
that will do the trick! 🙂
Hi hayzer,
By your statement
declare @abc table
(
name1 varchar(20)
)
insert into @abc values('JOHN-1234')
insert into @abc values ('JOHN/1234')
insert into @abc values ('JOHNX/1234-2')
insert into @abc values ('JOHNY/1234/2')
--
select left(name1,charindex('-', name1)-1)
from @abc
where name1 like '%-%'
union all
select left(name1,charindex('/', name1)-1)
from @abc
where name1 like '%/%'
--
RESULT
JOHN
JOHNX/1234
JOHN
JOHNX
JOHNY
Because of these results only, flo wrote all possible.
ARUN SAS
hi arun,
if you want all possible results, including those in the same row:
for example: 'Gotcha?-1214/34' will return 2 results which is --
Gotcha?
Gotcha?-1214
this code will give you all possible results:
declare @abc table
(
name1 varchar(20)
)
insert into @abc
select 'jones/1234' union all
select 'Jones-1234' union all
select 'McDonald/56780' union all
select 'Goddard-098765' union all
select 'Gotcha?-1214/34' union all
select 'HaveNow!/7896-7' union all
select 'NoSplit'
--
select name1, left(name1,charindex('-', name1)-1)
from @abc
where name1 like '%-%'
union all
select name1, left(name1,charindex('/', name1)-1)
from @abc
where name1 like '%/%'
union all
select name1, name1
from @abc
where not name1 like '%-%'
and not name1 like '%/%'
results:
Jones
Goddard
Gotcha?
HaveNow!/7896
jones
McDonald
Gotcha?-1214
HaveNow!
NoSplit
i doubt though if it will run smoothly if the data will be in tens of thousands...:hehe:
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply