question - 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

General SQL Server Forums

New to SQL Server Programming

question

Author

Topic

Ahmadibrahim
Starting Member

3 Posts

Posted - 2012-07-27 : 08:46:53

Hello All,

i need your help
while
i'm trying to run this procedure update [Ogero Data].[dbo].[ogero] Set Street = substring(address,CHARINDEX('?.',address),CHARINDEX('?.',address)-CHARINDEX('?.',address));

i got this error
Msg 536, Level 16, State 5, Line 4
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

could somebody please help me many thanks for your help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-27 : 08:51:02

Can you please give data type and example data for column address and can you please explain what you want to get?

No, you're never too old to Yak'n'Roll if you're too young to die.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-27 : 08:58:30

CHARINDEX('?.',address)-CHARINDEX('?.',address)
This is the length and looks like it will always be 0 so will give that error
maybe yoou want
CHARINDEX('?.',address)-CHARINDEX('?.',address,CHARINDEX('?.',address)+1)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ahmadibrahim
Starting Member

3 Posts

Posted - 2012-07-27 : 09:01:31

hi Fred first many thank to your reply

address is nvarchar(250) .

actually ? is an Arabic Character but it does not appeared on the website correctly .

all what i want to do is update the street to take the convenient phrase from address column

Ahmadibrahim
Starting Member

3 Posts

Posted - 2012-07-27 : 09:11:44

dear Fred ,

CHARINDEX('?.',address)-CHARINDEX('?.',address)

the characters in two charindex function are different and I'm sure that the difference is not null.

i have made a select query to see the difference amount and it's >0

but it seems that the type returned is not suitable for substring function.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-27 : 09:16:32

Then please, just to make it easier (and visible) for us: post example data that can be displayed without problems.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 10:17:36

quote:
Originally posted by Ahmadibrahim

dear Fred ,

CHARINDEX('?.',address)-CHARINDEX('?.',address)

the characters in two charindex function are different and I'm sure that the difference is not null.

i have made a select query to see the difference amount and it's >0

but it seems that the type returned is not suitable for substring function.

this will always return 0 as both charindex will return first occurance position of the character

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 10:20:43

quote:
Originally posted by Ahmadibrahim

dear Fred ,

CHARINDEX('?.',address)-CHARINDEX('?.',address)

the characters in two charindex function are different and I'm sure that the difference is not null.

i have made a select query to see the difference amount and it's >0

but it seems that the type returned is not suitable for substring function.

instead of ? use unicode values for characters and use char(value)

inside charindex

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-27 : 11:04:32

quote:
Originally posted by Ahmadibrahim

hi Fred first many thank to your reply

address is nvarchar(250) .

actually ? is an Arabic Character but it does not appeared on the website correctly .

all what i want to do is update the street to take the convenient phrase from address column

In that case use N'?.' otherwise everthing is converted to ascii.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources