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

Transact-SQL (2000)

CHARINDEX

Author

Topic

sezzy
Starting Member

3 Posts

Posted - 2005-04-12 : 08:42:21

I'm trying to use this statement to find the last word in a string.

SELECT RIGHT(Ad1, CHARINDEX(' ', REVERSE(Ad1))-1)
FROM Address

It works fine but only on 15 rows and then I get this error:

Server: Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.

If I try using a select top 5 and then run it it works fine. Anyone know why this is happening?

There is 14.5 million records in the table I'm querying is that something to do with it?

Thanks for any help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-12 : 08:45:10

what's the value of the 16th row? null or empty sttring?
because then CHARINDEX(' ', REVERSE(Ad1))-1 would return either null or 0

Go with the flow & have fun! Else fight the flow

sezzy
Starting Member

3 Posts

Posted - 2005-04-12 : 08:51:13

I've just had a look and the 15th row has just one word in it with no space, so I think that is causing the problem! Dunno how to get round that problem as I want it to pick up those words aswell.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-12 : 08:59:55

will this do?


declare @table table (ad1 varchar(100))
insert into @table
select 'gwegew 543252' union all
select 'twgese' union all
select 'g4wga gfw4t4w 4325wetey' union all
select '' 

SELECT 	RIGHT(Ad1, case when CHARINDEX(' ', REVERSE(Ad1)) > 1 then CHARINDEX(' ', REVERSE(Ad1))-1 else len(Ad1) end)
FROM 	@table

Go with the flow & have fun! Else fight the flow

sezzy
Starting Member

3 Posts

Posted - 2005-04-12 : 09:32:16

That worked perfectly. Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources