How to find Uppercase text in a text field

[Iblues]

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

[Rabbit]

Do you mean match a certain set of upper case two letter abbreviations? Or return any upper case two letter abbreviations?

Either way you will need to collate the field in the query as case sensitive if it's not already stored that way. You can use COLLATE SQL_Latin1_General_CP1_CS_AS to do that. Then you can use the PATINDEX() function to find the location. Now that you know where it occurs in the string, you can use the SUBSTRING() function to pull out the two letter code.

Whether or not you have a preset list of abbreviations will only affect how you set up the pattern to match.

There are some caveats like how to account for strings that don't have any such pattern or how to account for when the pattern occurs at the beginning or end of a sentence. But those can be worked out as we come to them. The important part is to get the main logic worked out.

[limweizhong]

You first need the field(s) to be case sensitive, as Rabbit mentioned, then you might want to list all the abbreviations in a separate table, then replace all non-alphabet characters with " " using a CLR function, adding " " to the front and back, then joining to your abbreviations table using a join condition that looks like field1 like "% " + field2 + " %".