'PARSENAME' not working

• SQLalchemy

  SSC-Addicted

  Points: 486

  More actions

  July 23, 2014 at 2:46 pm

  #397107

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 23, 2014 at 3:09 pm

  #1731956

  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;

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 23, 2014 at 3:27 pm

  #1731962

  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;