CHARINDEX - matching AND non-matching results

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)

CHARINDEX - matching AND non-matching results

Author

Topic

CactusJuice
Starting Member

46 Posts

Posted - 2005-02-16 : 14:35:38

I have a column that may contain a series of 2 or more whitespace characters. If it does, I want to take the name only. If it doesn't then I just want the first 32 characters. For example, a record may look like this (For readability I've used underlines to represent the whitespace):
Julie Smith_____President and CEO

I only want the person's name, not their title. So I wrote the following expression that looks for the first occurance of more than one whitespace:
SELECT @NewString = LEFT(@StringName, CHARINDEX('__', @StringName, 0))

But some entries such as below do not have white space between the name and title (so I just want the first 32 characters). But since there is no match because CHARINDEX evaluates to 0.
Stephen Potter Account Executive

This is twisting my brain in knots

Cameron

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-16 : 15:31:00

The answer to your immediate question is just a CASE statement. Parsing what should be multiple fields can be tricky. Ideally that should have been entered into several fields like: Prefix, FirstName, MiddleName, LastName, suffix, Title.


SELECT @NewString = Case
		    when CHARINDEX('  ', @StringName) > 0 
		    then LEFT(@StringName, CHARINDEX('  ', @StringName, 0))
		    Else substring(@StringName, 1, 32) 
		    End

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources