search for uppercase character in string

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

Transact-SQL (2000)

search for uppercase character in string

Author

Topic

gerrif55
Starting Member

2 Posts

Posted - 2004-09-06 : 02:44:46

How can I easily search for an uppercase character in a string, to be able to separate two or more words

eg. separate: "ClientRegisteredName"
into: "Client" and "Registered" and "Name"

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-06 : 04:56:53

select substring(column1,1,6) as client, substring(column1,7,10) as registered, substring(column1,17,4) as name from table1
would get you a SOLUTION IN THIS PARTICUALR INSTANCE....

but that would depend on the lengths of each word being 5,10 and 4 respectively.

if that's not the case....and you are delimiting/identifying the start of each word by the presence of an uppercase character....

then 1stly you need to have a 'case sensitive' enabled table/database
and then you need a repetitive search for characters in the required ascii range...ie...asc(a) <> asc(A)...1 search for each word.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-06 : 05:04:52

use a range:
if ascii(letter) between 65 and 90 then it's an uppercase

here's an example from BOL
------------
Examples
This example, which assumes an ASCII character set, returns the ASCII value and char character for each character in the string "Du monde entier."

SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'Du monde entier'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
SET NOCOUNT OFF
GO

------------

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-06 : 05:08:43

You could do this


declare @var varchar(255)

select @var = 'ClientRegisteredName'

select 	Ltrim (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	Replace (
	replace (@var COLLATE Latin1_General_BIN 
	,'A', ' A')
	,'B', ' B')
	,'C', ' C')
	,'D', ' D')
	,'E', ' E')
	,'F', ' F')
	,'G', ' G')
	,'H', ' H')
	,'I', ' I')
	,'J', ' J')
	,'K', ' K')
	,'L', ' L')
	,'M', ' M')
	,'N', ' N')
	,'O', ' O')
	,'P', ' P')
	,'Q', ' Q')
	,'R', ' R')
	,'S', ' S')
	,'T', ' T')
	,'U', ' U')
	,'V', ' V')
	,'W', ' W')
	,'X', ' X')
	,'Y', ' Y')
	,'Z', ' Z')
	)

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

gerrif55
Starting Member

2 Posts

Posted - 2004-09-06 : 23:53:14

Thanks Jen, much appreciated...

Here is a solution that separates 'ClientRegisteredName'
into 'Client' 'Registered' and 'Name'.




declare  @word varchar(20)
select   @word = 'ClientRegisteredName'
DECLARE  @WordTwoPos int, @WordThreePos int
SET      @WordTwoPos = 2

WHILE    @WordTwoPos <= DATALENGTH(@word)
BEGIN
    if ascii(SUBSTRING(@word, @WordTwoPos, 1)) between 65 and 90
        break    
    else
    SET @WordTwoPos = @WordTwoPos + 1
END

Set @WordThreePos = @WordTwoPos + 1

WHILE    @WordThreePos <= DATALENGTH(@word)
BEGIN
    if ascii(SUBSTRING(@word, @WordThreePos, 1)) between 65 and 90
        break    
    else
    SET @WordThreePos = @WordThreePos + 1
END

select  substring(@word, 1, @WordTwoPos -1) as Word1,
        substring(@word, @WordTwoPos, @WordThreePos - @WordTwoPos) as Word2,
        substring (@word, @WordThreePos, len(@word)-@WordThreePos+1) as Word3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources