How to find uppercase codes in a text field

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)

How to find uppercase codes in a text field

Author

Topic

iblues
Starting Member

1 Post

Posted - 2012-04-06 : 11:01:43

I've a table column that stores the two digit uppercase state and city codes (example: FL for Florida; KC for Kansas City). I want to run a SQL query (not function or procedure) to extract these uppercase codes from the text.

Example: Here are 3 records showing values of the desciption column:

1. KC manufacutring unit provide LCDAD equipments.
2. All the tests are performed at FL unit.
3. The biggest TIN35 plant is in CA.

Here is the output I am trying to get:

State/City Description
---------- ---------------------------------------
KC KC manufacutring unit provide LCDAD equipments.
FL All the tests are performed at FL unit.
CA The biggest TIN35 plant is in CA.

I would greatly appreciate your time and input.

Thanks,
AN

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-06 : 12:17:33

If the state codes are known in advance (and I can't imagine why they are not), you can create a table with all the state codes and join on that table like this:


CREATE TABLE USStates(state_code CHAR(2));
INSERT INTO USStates VALUES ('FL'),('NY'),('CT');

SELECT
	u.state_code,
	y.col1
FROM
	YourTable y
	INNER JOIN USStates u ON y.col1 LIKE '% '+u.state_code+' %' COLLATE SQL_Latin1_General_CP1_CS_AS;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources