Invalid parameter - 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)

Invalid parameter

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-01-19 : 14:51:22

Could someone tell me why this statement
does not work

" Invalid length parameter passed to the substring function."

When I enter the following statement

"select substring(suaddress,1,len(suaddress)-1) as add1 from pl_accounts"

isanlu
Starting Member

7 Posts

Posted - 2005-01-19 : 15:03:55

Looks like you may have a row in there that is either Null or blank and therefore you are returning a negative len for that row.

Try looking for the row by doing this

SELECT *
FROM TABLE_NAME
ORDER BY len(suaddress)to see if there are any 0

isanlu
Starting Member

7 Posts

Posted - 2005-01-19 : 15:13:07

Better Yet

SELECT *
FROM TABLE_NAME
WHERE len(suaddress)< 1

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-19 : 16:32:46

Try this:


select substring(COALESCE(suaddress, ' '),1,len(COALESCE(suaddress, ' '))-1) as add1 from pl_accounts

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources