extract only uppercase characters

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)

extract only uppercase characters

Author

Topic

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-09-25 : 10:01:10

Hi all,

is there a way to select all uppercase characters before a delimiter from a string field?

I have a delimiter of ~ in a string field and there is always between 2 and 5 uppercase characters preceding this delimiter which I would like to extract.

string field looks like this
AB~Text CDE~Text FGHIJ~More text

Many thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-25 : 12:23:45

You can distinguish upper and lower case by using either an explicit COLLATION (See BOL for details) or by converting to varbinary (See BOL for details). Personally, I'd use COLLATION.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-10-06 : 09:35:47

Hi Bustaz Kool,
firstly what is BOL?
and also, I've looked up COLLATION but cannot find any decent examples. Could you possibly give me an example of using COLLATION?

Many thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-06 : 14:29:31

BOL = Books Online (It's what appears when you bring up SQL Help).
If all you want to do is grab everything before the first "~" then you can use CHARINDEX to find the delimiter

select case 
   when CHARINDEX('~', MyColumn) = 0  -- Not Found
   then MyColumn
   else SUBSTRING(MyColumn, 1, CHARINDEX('~', MyColumn) - 1)
   end
from MyTable

If you need to guarantee that the leading characters are all upper case you could test this by comparing their varbinary values:

if cast(result as varbinary(100)) = cast(UPPER(result) as varbinary(100))
  -- they are all upper case

No amount of belief makes something a fact. -James Randi

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-10-07 : 05:33:35

Thanks for the reply.
What I need to achieve is the following:

the capital letters before each ~ are codes and the text after each ~ are their respective descriptions. Unfortunately the system records these codes and descriptions in one long string field.
I need to extract each code and it's respective descriptions.

Hope that makes sense.

Many thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-07 : 18:01:20

If you can guarantee that there will always be the delimiter...

declare @MyValue varchar(100) = '909~Riverside';

select
   SUBSTRING(@MyValue, 1, CHARINDEX('~', @MyValue) - 1) MyCode,
   SUBSTRING(@MyValue, CHARINDEX('~', @MyValue) + 1, len(@MyValue) - CHARINDEX('~', @MyValue)) MyDescription

Otherwise, you'll need to test for its existence first.

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources