Web Development

Ask something
Discussion Locked

MS SQL Server and - sign

0 helpful
10 replies

by jim.schromm 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
One of our SQL Servers will not convert a character field containing a number with a "-" trailing to a negative number. It does not recognize it as a number at all if you run ISNUMERIC. However another one of our SQL Servers does recognize this formatting is a number. What was set up differently and how do I fix it?

10 replies

by peter ritchie

Are you having the problem only with ISNUMERIC or with ISNUMERIC and data entry?

I tried a couple of SQL Servers and found that ISNUMERIC did not not recognize "1-" but I could enter "1-" into a numeric column and it was converted to "-1".

Oddly enough, even if I changed the negative number format in the Control Panel Regional settings to a trailing sign, ISNUMERIC still didn't recognize "1-" not displayed negative one as "1-".

by jim.schromm

I'm not able to load this type of data via a DTS or by using SQL. I open the table in SQLServer and make the change by using 1- but it would not work as a SQL Query using update. It is interesting that the regional settings does not affect ISNUMERIC.

by lwachs

Have you checked to see what the problem server's Negative Number Format is set to? (Number tab of the Regional Settings in Control Panel). I can't swear that this is it, but it would be the first place I would check.

Best of luck!

by lwachs

I guess I should have read a little more carefully. Now I see that you tried it already -- never mind!

by limbo

Is "1-" Numeric? No.

I dispute your ability to pass this through the ISNUMERIC function to return TRUE - ever.

As a soultion, how good is your string manpiulation knowledge?

In SQL...

SELECT RIGHT('1-',1) + LEFT('1-',LEN('1-') - 1)

As an ActiveX Script for DTS consider...

Dim Length
Dim Alpha
Dim Numeric

Function Main()
Length = Len(DTSSource("Col001"))
Alpha = Right(DTSSource("Col001"),1)
Numeric = Left(DTSSource("Col001"),Length - 1)

If Alpha = "-" ThenDTSDestination("Col001") = Alpha & Numeric
Else
DTSDestination("Col001") = DTSSource("Col001")
End If

Main = DTSTransformStat_OK
End Function

That'll do the job ;)

This discussion is closed to new replies.