SQL question. Decimal separator "," or "."

JesperMP

Lifetime Supporting Member + Moderator
JesperMP
Join Date
Feb 2003
Location
ᚴᚬᛒᛅᚾᚼᚬᚠᚾ
Posts
16,307
Hi.

Here is a good one for the SQL experts.
I found out the hard way that I had to set the decimal separator to "." for my script code to work for writing REALs to the database. I connect to the SyBase database server which is on another PC.

Does SQL always use the "." ?
Or is it configurable ?
Or do SQL client programs normally handle this in a "smart" way ? If so how ?
 
Until now I have achieved to get an error message, if the "wrong" symbol is set on the PC.

chDecPoint = Mid(CStr(8.1), 2, 1)
If SmartTags("ODBC\debug_message_on") And Not chDecPoint = "." Then ShowSystemAlarm "Decimal separator is wrong. Set to '.' in Windows Control Panel .. Regional settings."

However, I would prefer to know if the decimal symbol is always ".".
And is there a smarter way to handle this ?
 
Hmmm.

The tables exist already, it is a 3rd party's DB. So I cannot alter it.
But this more or less tells me that the decimal separator isnt fixed.
This complicates matters. If that is true I just cannot assume that it has to be "."

o_O

edit: Can I somehow retrive the decimal separator from the tables ?
 
Jesper,

Sometimes you need to change the terminating terminal.

SET TERM

SET TERM is special in that it changes the statement termination character. The default statement termination character is a semi-colon. However, if you want to create or alter a Stored Procedure or Trigger, the semi-colons contained in the body would also terminate the command that define the procedure. So to define a stored procedure:


  • Use SET TERM to change the statement termination to something that does not occur in your procedure
  • Create or alter your Stored Procedure or Trigger
  • Terminate this command with the new termination character
  • Change the termination character back to a semi-colon using SET TERM
Does this help at all.

Paul.
 
I think I get the gist of what you are saying, pouch.
The two important characters are the decimal point and the terminator (aka. "separator" ?). It is these two that have to match in both systems.

But to use your suggestion, I would have to know what the termination character is already. If not, I would alter the 3rd partys database, wouldnt I ?
And I still do not know what the decimal character is.

I think the solution is to retrieve the active decimal character and terminator characters, and then use these in my script.
 
Hi Jesper

use sql command

select convert(sysname,DatabasePropertyEx(db_name(),'Collation'))

to determine the cyrrent collation. Then you can determine if . or , are used in your scripting


Cheers


edit: there should be no space in the word Collation
 
hi cjd.

Bear with me, but I am a complete noob at SQL.

Are sysname, databasepropertyex and db_name variables ?
If so, what should i link the variables with ?
What does select convert do exactly ?

I did google "select convert", but didnt get any clear answer from that.
 
hi Jesper

Run the whole line as an sql query. The returned value will be the 'language' of the database file.

Open your DB in sql express managment tool and cut and paste the line

select convert(sysname,DatabasePropertyEx(db_name(),'Collation'))


directly into the query pane and execute it.

The collated language is returned in MS Speak



So if you run the command first, you can determine if a comma or a period is required based on whether language xx or language yy is returned
 
Hi Jesper
I have found out that if you have an existing table and then collate it the existing data stays as it is so this is probably not the solution you are looking for.

Maybe try posting the question on a microsoft sql forum

The query in #9 above will not change anything, but will provide a method of testing which seperator is in use

Cheers
 
Not sure you mean with "the existing data (in the database ?) stays as it is". It would be best if I didnt change anything in the existing database apart from that I insert data into existing tables. So if the data stays as it is would be fine with me.

I was hoping that there was something similar like the VBS code I mentioned before:
chDecPoint = Mid(CStr(8.1), 2, 1)
 
I have always thought that "collation order" is a parameter of SQL client, not of the database server. In other words, it cintrols the way the existing data would be represented without changing the actual data.

If I am not mistaken, this is one of the database connection parameters; in most cases people leave it at the default value, but in this case it looks like it has to be explicitly specified. So any numeric values will look like they have a decimal point regardless of the fact they had been stored in the database with a decimal comma.
 
I am going to duck out of this, I not an 'expert' but as I understand it collation is used to determine the locale of either the database, a table within it or an individual column in a table.

Having read a bit deeper there is mention of the 'strings' so am now thinking that the collation is for things like german umlauts and french accents etc rather than the comma/period problem.

Apologies if I have wasted time for you. I would suggest you post the question on a SQL Server forum

Cheers
 
OK. Now I have posted on an SQL forum. Awaits impatiently someone to answer.
In case you are interested:
http://www.eng-tips.com/viewthread.cfm?qid=210787&page=1

In the meantime I found this on www.quackit.com (thanks manmeetvirdi for the link):
About UPDATE:
"To update multiple fields, separate each field assignment with a comma."
That more or less says that the comma is always the separator. And then I cleverly deduct that the period is always the decimal.

I'll await the SQL gurus response though.
edit: It makes sense. It would be crazy if these things could be configurable.
 
Last edited:

Similar Topics

U
SQL Server question
Hello, Anyone knows why the objectname isn't accepted ? Thanks in advance, kind regards, G
Replies
5
Views
1,810
userxyz
U
TConnolly
RSView Access VBA/SQL question
I'm using RSView VBA to record the last parameter adjustments made for a given product in an MS Access data base. The Access table contains...
Replies
4
Views
2,337
ttichy
T
B
InSQL Logging Snapshot Question
Hi, Just looking for some info on the viabillity of this approach: We currently have a Wonderware/S7 system that generates reports at the end of a...
Replies
0
Views
1,435
BillyB
B
J
SQL, RSView Question....
Hello All, I have searched and have read many posts until I am a little more than confused. I need to log data from two separate pieces...
Replies
1
Views
3,304
surferb
S
D
Unable to connect FTV SE to SQL EXPRESS Server
Hi all, I'm having difficulties trying to connect FactoryTalk View SE Local Station (V13.00) to MS SQL Server Express. I state that they are...
Replies
2
Views
131
Cheeseface
C
Back
Top Bottom