Update script to convert lower case data

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 2008 Forums

Transact-SQL (2008)

Update script to convert lower case data

Author

Topic

choideyoung
Starting Member

10 Posts

Posted - 2012-07-02 : 16:13:49

I am trying to write a script that will convert lower case data to a new code. For example I have a table in which "o" and "O" are stored. I am looking for a way to find and convert all "o" into "OO".

I have a script that finds either upper or lower but not sure on the update part.

Thanks for any assistance.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-02 : 16:21:57

is this in one column or multiple columns? is this anywhere o is found or only when o is in the beginning of a word or in the middle of a word

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

choideyoung
Starting Member

10 Posts

Posted - 2012-07-02 : 16:30:47

This is just in one column...and it would be anytime I find a lowercase "o" I would need to convert it to "OO".

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-05 : 12:19:05

declare @ooo table(ohplease varchar(max) COLLATE Latin1_General_CS_AS)

insert @ooo
select 'oh so you want to remove good old OOs'

SELECT replace( ohplease, 'o', N'OO'),ohplease
from @ooo

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 13:01:27

quote:
Originally posted by choideyoung

This is just in one column...and it would be anytime I find a lowercase "o" I would need to convert it to "OO".

so if its
of
will it become OOf

or do you want to replace only individual occurances of o to OO?

SELECT replace( ohplease, 'o', N'OO') AS AllOccuranceReplace,
replace( ohplease, ' o ', N'OO') AS IndivOccuranceReplace,
ohplease
FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-05 : 13:11:59

OP said "This is just in one column...and it would be anytime I find a lowercase "o" I would need to convert it to "OO"."

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 14:28:10

quote:
Originally posted by yosiasz

OP said "This is just in one column...and it would be anytime I find a lowercase "o" I would need to convert it to "OO"."

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

It still can be either one of above

Lets wait till we get confirmation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

choideyoung
Starting Member

10 Posts

Posted - 2012-07-09 : 14:39:14

Thank you for the responses…to clarify a little more... here is what I am looking for. I have a column in a table that holds hospital codes.

Such as:

o = Observation Bed
O = Occupational Therapy

While I can load those codes into the SQL table fine…my application does not recognize those as unique codes. What I would like to do is write a script that finds any “o” and converts that to “OO” therefore making it a unique code for the application. So my end results would look like this:

OO = Observation Bed
O = Occupational Therapy

Thanks for any help you can provide

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 14:54:46

Doesn't what Yosiasz proposed work for you? Below is an example:

CREATE TABLE #tmp(id INT, code VARCHAR(2));
INSERT INTO #tmp VALUES (1,'o'),(2,'O'),(3,'o');

-- if you want to keep the data in the base table unchanged and 
-- return the mapped codes to the client
SELECT
	id,
	CASE WHEN 'o' = code COLLATE Latin1_General_CS_AS THEN 'OO' ELSE code END
FROM
	#tmp;

-- if you want to update the data in the base table.	
UPDATE #tmp SET 
	code = CASE WHEN 'o' = code COLLATE Latin1_General_CS_AS THEN 'OO' ELSE code END

SELECT * FROM #tmp;

DROP TABLE #tmp;

My default collation is not case sensitive, so I am forcing it to be case-sensitive in the comparison.

choideyoung
Starting Member

10 Posts

Posted - 2012-07-09 : 15:17:24

so would the correct string (with my table and column names) be this?

UPDATE T_OP_ENCOUNTER
SET
AdmitService = CASE WHEN 'o' = AdmitService COLLATE Latin1_General_CS_AS THEN 'OO' ELSE AdmitService END

With the plan being to update the source data.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 15:45:27

Yes. But please test if it works as expected in a test environment.

choideyoung
Starting Member

10 Posts

Posted - 2012-07-09 : 16:22:51

quote:
Originally posted by sunitabeck

Yes. But please test if it works as expected in a test environment.

Yes I tested that in a temp table and it seemd to do what I need.

Thank you for your help

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-10 : 10:03:02

this is in order to get yourself unique hospital codes? does not sound very resilient code naming. I would think there should be some industry standard for these codes. I would think you would have a numeric code along with a descriptive text code. then there would never be a need to do what you are trying to do. good well thought out design up front saves you lots of headaches down the line

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources