Checking for special characters

When passed a last name as input into a stored procedure I need to verify the
last name in a field containing the last name and location. However the last
name is the first word starting from the left up until a space or a special
character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
Thomas*Texas etc). I just want to go through the table and find matches for
"Thomas". I tried to use char index and substring but I recieve the error
"Invalid length parameter passed to the substring function" when checking
for a special character that is not present. Does anyone know of an easy way
to accomplish this? Thanks in advance.
Gordon
Hi Gordon
"gordon" wrote:

> When passed a last name as input into a stored procedure I need to verify the
> last name in a field containing the last name and location. However the last
> name is the first word starting from the left up until a space or a special
> character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
> Thomas*Texas etc). I just want to go through the table and find matches for
> "Thomas". I tried to use char index and substring but I recieve the error
> "Invalid length parameter passed to the substring function" when checking
> for a special character that is not present. Does anyone know of an easy way
> to accomplish this? Thanks in advance.
> Gordon
You could use a function to determine the length or the appropriate
substring such as
CREATE FUNCTION dbo.GetFirstName(@.name varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @.returnname varchar(50)
SELECT @.returnname = LEFT(@.name,len)
FROM
( SELECT MIN(len)-1 AS len
FROM
( SELECT CHARINDEX('-',@.name) as LEN
UNION ALL SELECT CHARINDEX('*',@.name)
UNION ALL SELECT CHARINDEX('_',@.name)
UNION ALL SELECT CHARINDEX('/',@.name)
UNION ALL SELECT CHARINDEX(SPACE(1),@.name)
UNION ALL SELECT LEN(@.name)+1 ) A
WHERE len > 0 ) l
RETURN (@.returnname)
END
SELECT dbo.GetFirstName(name)
FROM
( SELECT 'George*Texas' AS name
UNION ALL SELECT 'Thomas-Hawaii'
UNION ALL SELECT 'Arnie California' ) a
John