General discussion

Locked

MS SQL Server and - sign

By jim.schromm ·
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?

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

MS SQL Server and - sign

by Peter Ritchie In reply to MS SQL Server and - sign

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-".

Collapse -

MS SQL Server and - sign

by jim.schromm In reply to MS SQL Server and - sign

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.

Collapse -

MS SQL Server and - sign

by shmaltz In reply to MS SQL Server and - sign

Check that data types of the field SQL stores in.

Collapse -

MS SQL Server and - sign

by jim.schromm In reply to MS SQL Server and - sign

Poster rated this answer

Collapse -

MS SQL Server and - sign

by Laura Wachs In reply to MS SQL Server and - sign

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!

Collapse -

MS SQL Server and - sign

by Laura Wachs In reply to MS SQL Server and - sign

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

Collapse -

MS SQL Server and - sign

by jim.schromm In reply to MS SQL Server and - sign

Seetings were ok.

Collapse -

MS SQL Server and - sign

by Limbo In reply to MS SQL Server and - sign

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

Collapse -

MS SQL Server and - sign

by jim.schromm In reply to MS SQL Server and - sign

Poster rated this answer

Collapse -

MS SQL Server and - sign

by jim.schromm In reply to MS SQL Server and - sign

This question was closed by the author

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums