IsNull use with CharIndex? - SQL Server Forums

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

SQL Server Development (2000)

IsNull use with CharIndex?

Author

Topic

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-04-09 : 16:51:31

------------------------------------------------------------------
DECLARE @A varchar(50)
DECLARE @B varchar(50)

SET @A='Smith'

SELECT @B=Substring(@A, 1, IsNull(CharIndex(' ', @A),Len(@A)))

PRINT @B
------------------------------------------------------------------
Hi Everyone,

This is a very basic example but a good one that demonstrates what I am trying to do. I am basically looking at Last Names and trying to split them into a list of LAST_NAME and SUFFIX. Some records have a suffix and some do not. I was trying the IsNull function as done above
to select out the last names and when there is not a suffix present like above, I desire it to accept the whole character string, but this does not work. When there is not a suffix or space present, nothing gets printed as @B. I solved this with the code below but I guess I just wanted to see if it could be done with IsNull or something cleaner.

Thanks in advance,
Anthony

-------------------------------------------------------------
DECLARE @A varchar(50)
DECLARE @B varchar(50)

SET @A='Smith Jr'

SELECT @B=Substring(@A, 1, Len(@A))

IF @B LIKE '% %'
BEGIN
SELECT @B=Substring(@B, 1, CharIndex(' ',@B))
END
PRINT @B
--------------------------------------------------------------

motokevin
Starting Member

36 Posts

Posted - 2002-04-09 : 17:13:21

This might be a little cleaner for you:

DECLARE @A varchar(50)
DECLARE @Surname varchar(50)
DECLARE @Suffix varchar(50)

SET @A='Smith JR' -- name
SET @Surname=''
SET @Suffix=''

SELECT @Surname=Case
When CharIndex(' ', @A)=0 then @A
Else
Substring(@A, 1, (CharIndex(' ', @A)))
End,
@Suffix=Case
When CharIndex(' ', @A)=0 then ''
Else
Substring(@A, (CharIndex(' ', @A)),Len(@A)-(CharIndex(' ', @A))+1)
End

Select @Surname,@Suffix

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources