Playing Doctor with Strings and Things

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

SQL Server 2000 Forums

Transact-SQL (2000)

Playing Doctor with Strings and Things

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-02 : 18:05:35

I have a joyful dilemma. Some of my doctors have the "MD" in their name. Some don't. What I want is for CK_PCPNAME to contain the name WITH an "MD" (I'd also like a column WITHOUT the "MD" in the name). Anyway, It seems to work, but Dr. Frankenstein's name isn't getting an "MD" in the CK_PCPNAME. Plus I get this error, too :
[Server: Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.]

Here's the sample code.....
DROP TABLE #A, #Doctors
CREATE TABLE #Doctors
(Specialty nvarchar (12) NULL,
PCPDesc nvarchar (20) NULL,
Status nvarchar (20) NULL)
GO
INSERT #Doctors VALUES ('Lobotomy ','De Jekyll MD,Haydn ','Overseas ')
INSERT #Doctors VALUES ('Colonoscopy ','Quetzalcoatl,Fay Beverly','On Site ')
INSERT #Doctors VALUES ('Psychiatry ','Cooper MD,Alice ','NASA ')
INSERT #Doctors VALUES ('Experimental','Frankenstein,Jack ','U.S.Navy ')
INSERT #Doctors VALUES ('Stereotomy ','Parsons MD,Alan ','On Tour ')
GO

SELECT SPECIALTY,PCPDesc, Upper(PCPDesc)as CK_PCPNAME, Upper(PCPDESC)as PCPNAME,
Upper(PCPDESC) as PCPLAST, Upper(PCPDESC) as PCPFIRST
INTO #A FROM #Doctors
GO

UPDATE #A SET PCPLAST = (SUBSTRING (PCPDESC,1,CHARINDEX(',',PCPDESC)))
GO

UPDATE #A SET PCPFIRST = SUBSTRING(PCPDESC,CHARINDEX(',',PCPDESC)+1,20)
GO

UPDATE #A SET CK_PCPNAME = LTRIM(PCPLAST)+' MD,'+RTRIM(PCPFIRST)
GO

SELECT * FROM #A
GO

Here's partial results....

SPECIALTY PCPDesc CK_PCPNAME PCPNAME PCPLAST PCPFIRST
------------ -------------------- -------------------- -------------------- -------------------- --------------------
Lobotomy De Jekyll MD,Haydn DE JEKYLL MD,HAYDN DE JEKYLL MD,HAYDN De Jekyll MD, Haydn
Psychiatry Cooper MD,Alice COOPER MD,ALICE COOPER MD,ALICE Cooper MD, Alice
Experimental Frankenstein,Jack FRANKENSTEIN,JACK FRANKENSTEIN,JACK Frankenstein, Jack
Stereotomy Parsons MD,Alan PARSONS MD,ALAN PARSONS MD,ALAN Parsons MD, Alan

Would someone please assist me in getting this right?

Thanks!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

DBCowboy
Starting Member

26 Posts

Posted - 2005-11-02 : 18:28:27

Increase the size of your column PCPDesc.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-02 : 18:35:16

BUT that only puts a DOUBLE "MD" in De Jekyll, Cooper & Parsons....I only want 1 "MD" there.

BUT IT's FIXED:

DROP TABLE #A, #Doctors
CREATE TABLE #Doctors
(Specialty nvarchar (12) NULL,
PCPDesc nvarchar (50) NULL,
Status nvarchar (20) NULL)
GO
INSERT #Doctors VALUES ('Lobotomy ','De Jekyll MD,Haydn ','Overseas ')
INSERT #Doctors VALUES ('Colonoscopy ','Quetzalcoatl,Fay Beverly','On Site ')
INSERT #Doctors VALUES ('Psychiatry ','Cooper MD,Alice ','NASA ')
INSERT #Doctors VALUES ('Experimental','Frankenstein,Jack ','U.S.Navy ')
INSERT #Doctors VALUES ('Stereotomy ','Parsons MD,Alan ','On Tour ')
GO

SELECT SPECIALTY,PCPDesc, Upper(PCPDesc)as CK_PCPNAME, Upper(PCPDESC)as PCPNAME,
Upper(PCPDESC) as PCPLAST, Upper(PCPDESC) as PCPFIRST
INTO #A FROM #Doctors
GO

UPDATE #A SET PCPLAST = (SUBSTRING (PCPDESC,1,CHARINDEX(',',PCPDESC)-1))
GO

UPDATE #A SET PCPFIRST = SUBSTRING(PCPDESC,CHARINDEX(',',PCPDESC)+1,20)
GO

UPDATE #A
SET CK_PCPNAME =
CASE
WHEN CHARINDEX('MD',CK_PCPNAME) <> 0 THEN LTRIM(PCPLAST)+','+RTRIM(PCPFIRST)
ELSE LTRIM(PCPLAST)+' MD,'+RTRIM(PCPFIRST)
END
GO

SELECT * FROM #A
GO

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-02 : 18:55:33

>>Would someone please assist me in getting this right?

Post the ideal output of select * from #A

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-02 : 18:57:51

quote:
Originally posted by TG

>>Would someone please assist me in getting this right?

Post the ideal output of select * from #A

Be One with the Optimizer
TG

SPECIALTY PCPDesc CK_PCPNAME PCPNAME PCPLAST PCPFIRST
------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Lobotomy De Jekyll MD,Haydn De Jekyll MD,Haydn DE JEKYLL MD,HAYDN De Jekyll MD Haydn
Colonoscopy Quetzalcoatl,Fay Beverly Quetzalcoatl MD,Fay Beverly QUETZALCOATL,FAY BEVERLY Quetzalcoatl Fay Beverly
Psychiatry Cooper MD,Alice Cooper MD,Alice COOPER MD,ALICE Cooper MD Alice
Experimental Frankenstein,Jack Frankenstein MD,Jack FRANKENSTEIN,JACK Frankenstein Jack
Stereotomy Parsons MD,Alan Parsons MD,Alan PARSONS MD,ALAN Parsons MD Alan

Every CK_PCPNAME entry has only one 'MD'. I posted my changes. Thanks, TG & DBCowboy!!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources