Appending a character in Last_Name with a condition.

Question

Appending a character in Last_Name with a condition.

wanxzhu

SSC Journeyman

Points: 98
More actions
June 14, 2013 at 8:03 am

#295352

I am new in SQL server database. I have scenario of deleting middle names in a table. There are two tables in my database to control patient name.
PT_RED has a field of ALS_FLG
PT_NAM has fields of PT_LST_NAM and PT_MID_NAM
The two tables has a primary key: INTNL_PT_NUM
Here is what I need.
1. If ALS_FLG is "Y" in PT_RED table, append "$" on PT_LST_NAM in PT_NAM table with the same INTNL_PT_NUM.
2. Remove PT_MID_NAM in PT_NAM table
Thanks for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

It's not that hard what you're trying to do and you can get help really fast. But to help you, we need more information about your tables and if possible, some sample data.

Read the article linked in my signature for guidance on getting better help. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

I see you used the word "patient" in your original post. Don't post any patient data!

Posting patient data in a public forum is very serious and would likely cost you your job. We don't want to see that happen for trying to get help.

What we really need is the DDL to create your tables and some sample data to reproduce this part of your environment so we can find a solution.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

wanxzhu SSC Journeyman Points: 98 More actions · Answer 3

Luis,

Thank you for your quick reply. Basically, there is one tables (PT_NAM) in the database I need to modify.

Here are 2 tables

PT_RED table

Column, Data

INTNL_PT_NUM, 276678

ALS_FLG, Y

PT_NAM table

Column, Data

INTNL_PT_NUM, 276678

PT_LST_NAM, ABCD

PT_FST_NAM, AAA

PT_MID_NAM, XYZ

I want Append a $ character to PT_LST_NAM (like ABCD$) if the ALS_FLG is Y. And then delete all PT_MID_NAM (XYZ) in PT_NAM table to make sure all PT NAMES are unique.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Now I am concerned. Do you really need to delete Patient information?

If it's just to show the names, then this might help.

WITH Unique_PT AS(

SELECTPT_LST_NAM + CASE WHEN ALS_FLG = 'Y' THEN '$' ELSE '' END AS PT_LST_NAM ,

PT_FST_NAM,

PT_MID_NAM,

ROW_NUMBER() OVER( PARTITION BY PT_MID_NAM ORDER BY PT_LST_NAM, PT_FST_NAM) rn

FROM PT_NAM N

LEFT

JOIN PT_RED R ON N.INTNL_PT_NUM = R.INTNL_PT_NUM

)

SELECT PT_LST_NAM,

PT_FST_NAM,

PT_MID_NAM

FROM Unique_PT

WHERE rn = 1

Be very careful on deleting and updating data just to make it "unique".

Please be sure to understand the code and ask anything you can't understand.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

wanxzhu SSC Journeyman Points: 98 More actions · Answer 5

Thank you Luis. Out hospital will definitely remove patient's middle name since it cause confusion with engine. One patient may have two or more records because of middle names. We cannot just remove the patients' middle name from database which will create duplicate name. I thought we first change the aliens to unique name and then remove middle name of all patients. From now on, all new patients will not have middle name any more.

I will let you know if I have problems to run your script. Thank you again.