Separating Last Name and Ext. - 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

Separating Last Name and Ext.

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-07-28 : 09:07:40

I have a report that I need to separate the last name (EDWARDS, JR) and the extension into two columns. I tried using a substring, but it is not working. Can someone tell me what I am doing wrong?

SELECT SUBSTRING([Last Name], 1, 1) AS Ext
FROM dhs_MonthEndProvidersREVISED
WHERE [Last Name] like '%Jr'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 09:10:52

Post full sample data with expected result

Madhivanan

Failing to plan is Planning to fail

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-07-28 : 09:17:53

Here are the ones that I need to separate and below is my statement. There are also ones that don't have an ext. and it just has a comma after the last name.

Last Name
ARLOTTI JR
ASHTON, JR
EGAN, JR
EL-MASRY
HAMATY, JR
HARTMAN, JR
HUWE, JR
LANSIGAN, JR
MILLER, SR
ALIZRAY, SR
MAGOVERN,

Here is the Statement......SELECT
pro.pro_id1,CONVERT(VARCHAR(10),
(pro.pro_chgdt), 101) AS [Update Date],
CONVERT(VARCHAR(10),
(dgh.prop_eff), 101) AS [Effective Date],
CONVERT(VARCHAR(10), (dgh.prop_trm), 101) AS [Termination Date],

[AHS] =
CASE
WHEN dgh.prop_trm is null THEN 'X'
WHEN dgh.prop_trm = '03/23/1977' THEN 'X'
WHEN dgh.prop_trm = '' THEN 'X'
WHEN GetDate() BETWEEN dgh.prop_eff AND dgh.prop_trm THEN 'X'
--WHEN GetDate() > dgh.prop_trm THEN 'X3'
ELSE ''
END,
[DoctorID] =
CASE dgh.prop_cont
WHEN '' THEN RIGHT(dgh.prop_id1,6) + RIGHT(dgh.prop_id2,2)
ELSE dgh.prop_cont
END,
--dgh.prop_cont AS [DoctorID],
pro.pro_upin AS [UPIN],
[Last Name] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN pro.pro_office
WHEN 'HOSP' THEN pro.pro_office
ELSE pro.pro_lname
END,

SUBSTRING([Last Name], 1, 1) AS Ext,

--pro.pro_lname AS [Last Name],
pro.pro_fname AS [First Name], pro.pro_minit AS [Middle Name],
'' AS [Ext], -- KZ confirmed this is not valued
pro.pro_degree AS [Degree],
'' AS [Degree2], -- KZ confirmed this is not valued
pro.pro_ssn AS [Social Security],
[Primary/Specialty] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN 'A'
WHEN 'BILL' THEN 'X'
WHEN 'EOTF' THEN 'X'

WHEN 'HOSP' THEN 'H'
WHEN 'PRIM' THEN 'S' --'P' KateZ: The only thing that should have a "P" is Family Practice, General Practice, Internal Medicine, & Pediatrics.
WHEN 'PROV' THEN 'S' --'P'
WHEN 'SPEC' THEN 'S'
ELSE 'X'
END, --pro.pro_type AS [Primary/Specialty],
CONVERT(VARCHAR(10), (pro.pro_dob), 101) AS DOB,
pro.pro_npi AS NPI,
pro.pro_spec1 AS [Spec 1], pro.pro_spec2 AS [Spec 2], pro.pro_spec3 AS [Spec 3],
pro.pro_tax1 AS [Tax ID],
pro.pro_office AS [Practice], pro.pro_addr1 AS [Address 1], pro.pro_addr2 AS [Address 2],
'' AS [Address 3], -- KZ confirmed this is not valued
pro.pro_city AS [City], pro.pro_state AS [St], pro.pro_zip AS [Zip],
replace(pro_county,' COUNTY','') + ' COUNTY' AS 'County', --pro.pro_county AS [COUNTY],
pro.pro_phone AS [Phone], pro.pro_fax AS [Fax #],
'' AS [Signed Contract], -- KZ confirmed this is not valued
'' AS [Comment], -- KZ confirmed this is not valued
pro.pro_email AS [email], pro.pro_www AS [URL], pro.pro_lat AS [LATITUDE], pro.pro_lon AS [LONGITUDE],
pro.pro_sex, pro.pro_type, pro.pro_geo, pro.pro_altid, CONVERT(VARCHAR(10), (pro.pro_eff), 101) as [pro.pro_eff],CONVERT(VARCHAR(10), (pro.pro_trm), 101) as [pro.pro_trm], pro.pro_xtyp, CONVERT(VARCHAR(10), (pro.pro_chgdt), 101) as [pro.pro_chgdt], pro.pro_btag, pro.pro_bref, pro.pro_pcs, pro.pro_spec1, pro.pro_spec2, pro.pro_spec3, pro.pro_phone,
dgh.prop_id1, dgh.prop_id2, dgh.prop_net, dgh.prop_prd, CONVERT(VARCHAR(10), (dgh.prop_eff), 101) as [dgh.prop_eff], CONVERT(VARCHAR(10), (dgh.prop_trm), 101)as [dgh.prop_trm],
dgh.prop_pcp, dgh.prop_prc, dgh.prop_uac, dgh.prop_cont, dgh.prop_disc, CONVERT(VARCHAR(10), (dgh.prop_chgdt), 101)as [dgh.prop_chgdt],
[PROVIDERTYPECODE] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN 'ANC'
WHEN 'BILL' THEN 'XX1'
WHEN 'EOTF' THEN 'XX2'
WHEN 'HOSP' THEN 'HOSP'
WHEN 'PRIM' THEN 'PHYS'
WHEN 'PROV' THEN 'PHYS'
WHEN 'SPEC' THEN 'PHYS'
ELSE 'XX3'
END,
[PROVIDERNAME] =
CASE pro.pro_xtyp
WHEN 'ANCI' THEN pro.pro_office
WHEN 'HOSP' THEN pro.pro_office
ELSE pro.pro_lname
END

FROM pro
JOIN prop dgh
ON pro.pro_id1 = dgh.prop_id1 AND dgh.prop_prd = 'DGH'
WHERE
pro.pro_xtyp NOT IN ('BILL', 'EOTF')
AND LEFT(dgh.prop_cont,2) <> 'NP' and
pro.pro_lname like '%Jr'

quote:
Originally posted by madhivanan

Post full sample data with expected result

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 09:33:59

select
left(last_name,charindex(',',last_name)-1), substring(last_name,charindex(',',last_name)+1,len(last_name))
from
table

Madhivanan

Failing to plan is Planning to fail

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 09:39:07

Hi check this,

select
left(([Last Name],charindex(' ',replace([Last Name],',',' '))-1)as LASTNAME,
right([Last Name],(len([Last Name])-charindex(' ',replace([Last Name],',',' '))))as EXT

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 09:42:14

Really sorry,
Check this

select
left([Last Name],charindex(' ',replace([Last Name],',',' '))-1)as LASTNAME,
right([Last Name],(len([Last Name])-charindex(' ',replace([Last Name],',',' '))))as EXT
from dhs_MonthEndProvidersREVISED

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 09:57:31

quote:
Originally posted by VGuyz

Really sorry,
Check this

select
left([Last Name],charindex(' ',replace([Last Name],',',' '))-1)as LASTNAME,
right([Last Name],(len([Last Name])-charindex(' ',replace([Last Name],',',' '))))as EXT
from dhs_MonthEndProvidersREVISED

this will break if the string dont contain a ','. you need to check condition for appearance of ',' and only if present use charindex to find position.

select 
left([Last Name],case when charindex(' ',replace([Last Name],',',' '))>0 then charindex(' ',replace([Last Name],',',' '))-1 else LEN([Last Name]) end)as LASTNAME,
case when charindex(' ',replace([Last Name],',',' '))>0 then substring([Last Name],charindex(' ',replace([Last Name],',',' '))+1,LEN([Last Name])) else null end as EXT
from dhs_MonthEndProvidersREVISED

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 10:47:10

hi visakh,

These are the combination posted,
'ARLOTTI,'
'ARLOTTI,JR'
'ARLOTTI,'
'ARLOTTI JR'

Kindly check with this combination.
and tell me if there any error in my query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 10:51:40

quote:
Originally posted by VGuyz

hi visakh,

These are the combination posted,
'ARLOTTI,'
'ARLOTTI,JR'
'ARLOTTI,'
'ARLOTTI JR'

Kindly check with this combination.
and tell me if there any error in my query.

your query will work fine for above. but there one data which is

EL-MASRY

this contains neither ',' nor ' '. so charindex will return 0 in this case and it causes -1 to be passed as length param to left and it will break. so you need to account for this condition also. thats why i used a case condition.

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 10:55:46

Hi visakh,

U r correct, my query will not work the data without ',',
i think by using the case statement,
we can overcome this problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 10:59:33

quote:
Originally posted by VGuyz

Hi visakh,

U r correct, my query will not work the data without ',',
i think by using the case statement,
we can overcome this problem.

thats exactly what i posted. refer to solution provided on 07/28/2008 : 09:57:31

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 11:01:40

Hi visakh,
Thanks alot for spending u'r time to
rectifying my mistake.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-28 : 11:21:42

http://weblogs.sqlteam.com/jeffs/archive/2008/06/11/golden-rule-of-data-manipulation.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 11:28:18