July 23, 2014 at 2:46 pm
I'm trying to learn how to use PARSENAME...
there is probably an easier way to do this but I'm really wanting to learn PARSENAME
I have the following DDL:
create table phoneNums (phone varchar(50));
insert into phoneNums values ('800-555-1212');
insert into phoneNums values ('888-555-1212');
insert into phoneNums values ('336-555-1212');
insert into phoneNums values ('804-555-1212');
insert into phoneNums values ('652-555-1212');
insert into phoneNums values ('704-555-1212');
insert into phoneNums values ('919-555-1212');
insert into phoneNums values ('252-555-1212');
insert into phoneNums values ('877-555-1212');
This is not working of course:
select parsename (phone,3) as PRE
from (select replace(phone, '-', '')) dt (phoneNums);
I'd like to see:
800
888
336
804
652
704
919
252
877
July 23, 2014 at 3:09 pm
This:
create table #phoneNums (phone varchar(50));
insert into #phoneNums values ('800-555-1212');
insert into #phoneNums values ('888-555-1212');
insert into #phoneNums values ('336-555-1212');
insert into #phoneNums values ('804-555-1212');
insert into #phoneNums values ('652-555-1212');
insert into #phoneNums values ('704-555-1212');
insert into #phoneNums values ('919-555-1212');
insert into #phoneNums values ('252-555-1212');
insert into #phoneNums values ('877-555-1212');
--This is not working of course:
--select parsename (phone,3) as PRE
--from (select replace(phone, '-', '')) dt (phoneNums);
select parsename(replace(phone, '-', '.'),3) from #phoneNums;
drop table #phoneNums;
July 23, 2014 at 3:27 pm
Thought I'd try and see if there was an alternative:
create table #phoneNums (phone varchar(50));
insert into #phoneNums values ('800-555-1212');
insert into #phoneNums values ('888-555-1212');
insert into #phoneNums values ('336-555-1212');
insert into #phoneNums values ('804-555-1212');
insert into #phoneNums values ('652-555-1212');
insert into #phoneNums values ('704-555-1212');
insert into #phoneNums values ('919-555-1212');
insert into #phoneNums values ('252-555-1212');
insert into #phoneNums values ('877-555-1212');
select parsename(replace(phone, '-', '.'),3) from #phoneNums;
-- Another option to get all three pieces. Not sure which would be faster:
select
left(phone,charindex('-',phone) - 1) FirstPart,
substring(phone, charindex('-',phone) + 1, (charindex('-',phone,charindex('-',phone) + 1) - charindex('-',phone)) - 1) SecondPart,
right(phone, len(phone) - charindex('-',phone,charindex('-',phone) + 1)) ThirdPart
from
#phoneNums;
drop table #phoneNums;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply