Regular expression help

[BOA_Dev]

Id like to do a select statement like this:

SELECT (case field when [^.]*.[^.]* then field else field+'.0') as field1
FROM table

this doesnt run and when I enclose the expression in apostrophes it just treats the expression like a string.

Basically I just want to check to see if a number is a whole number(no decimal point). If it is, I want to put a '.0' at the end of it. If it already has a decimal place, then I just leave it alone.

Im using SQL 2000 btw. In the examples I searched for online I didnt see reg expressions being used in a case statement.

 

[cjard]

"There is no version of SQL Server that supports regular expressions natively" -- http://www.sqlteam.com/item.asp?ItemID=13947

Google for xp_regex and install it.. But read this first:

Basically I just want to check to see if a number is a whole number(no decimal point). If it is, I want to put a '.0' at the end of it. If it already has a decimal place, then I just leave it alone

Your question doesnt make sense. Numbers are not stored with or without decimal places, and you cannot add one..

e.g. the following query will run, and update records, but you wont see decimal places:

UPDATE table SET numCol = 2.0 WHERE numCol = 2


What you must do is format your number on whatever report or screen youre showing it on..

 

[BOA_Dev]

Your question doesnt make sense. Numbers are not stored with or without decimal places, and you cannot add one..

But its a varchar. I just want to check to see if it has a decimal point (like 2.11) then I wouldnt add ".0" to it. But if it was a whole number(didn't contain a ".") then I would add a .0 to it.

But what do you mean numbers arent stored with decimal places? Im very confused!

I would really like to do this within the query but I guess I can't without reg expressions. And I don't have permissions to install anything on the server.

Thanks for the help!

 

[cjard]

But its a varchar.

Ugh.. you store numbers as strings? What do you do when you want to add them up? or sort them?

'10' + '10' = '1010'

Sorting:
'1'
'10'
'2'

I just want to check to see if it has a decimal point (like 2.11) then I wouldnt add ".0" to it. But if it was a whole number(didn't contain a ".") then I would add a .0 to it.

Um..

UPDATE table SET col = col + '.0' WHERE CHARINDEX('.', col) = 0

But what do you mean numbers arent stored with decimal places? Im very confused!

I mean a number, when stored as a NUMBER, doesnt have a decimal place stored along with it! It's digits with a precision! You cant update a number to have a decimal place (look at my update query) - i.e. you cant take all the number 2, and set them to be 2.0, because 2.0 is 2.. its not going to change a thing!

When we use databases, we are supposed to store data as relevant types, then we can work with them. If we need them formatted a certain way, like dd/MM/yyy or 0.000 then we do that when we get the value OUT, not when we put it in!

I would really like to do this within the query but I guess I can't without reg expressions. And I don't have permissions to install anything on the server.

Thanks for the help!

Looking at your regex, all it does is check for the existence of a dot - which makes your solution very complicated compared to mine.. ?

DONT STORE NUMBERS IN VARCHARS! (It makes them fat, slow and difficult to manipulate)

 

[BOA_Dev]

If we need them formatted a certain way, like dd/MM/yyy or 0.000 then we do that when we get the value OUT, not when we put it in!

Right thats what Im trying to do. When I pull the data out(the select statement in my post) Im formatting it to append a ".0" at the end.

I failed to mention the field is actually being stored as a float so the query actually looks something like this:

SELECT (CASE convert(VARCHAR,field) WHEN LIKE regexp then ... else ...) as field1


I just wanted the select statement to format the data the way I want it so I dont have to do any additional work in code. I just ended up using wildcard characters like this '%.%' and that seemed to work fine.

Thanks for all the help!

 

[cjard]

I just wanted the select statement to format the data the way I want it so I dont have to do any additional work in code. I just ended up using wildcard characters like this '%.%' and that seemed to work fine.

Thanks for all the help!

I'd recommend selecting them into VB as floats still, and then doing the final showing of them *in vb* as strings.

If you were going it in a grid, you'd format the cell with "0.0" or something.. To put in a text box, try for .ToString("0.0")

It's always best to load data from a db into a client app in a data type as close to the db as possible.. that way you dont get round trip conversion errors.. COnsider a date stored in a db as a date, with a time zone.. you select it out but convert it to a string, losing the timezone info. When the client, in another country works with it, the time is wrong, or when they save and update it, it is converted from string back to date, but again with the wrong timezone..

Always keep data in its stored form as long as possible (unless this is a one way op only; in which case having the database format the data can make your life less of a headache - though bear in mind that high end reporting packages like crystal, are much more flexible when working with numerical numbers, not numeric strings etc..)

ps; this looks nice:

http://www.novicksoftware.com/udfofweek/Vol1/T-SQL-UDF-Volume-1-Number-48-formatnumber.htm


And also I've seen reference to:

SELECT CAST(column AS DECIMAL(10,2)) FROM table

and it's output a number with 2 d.p. ii dont know if they were trunced or rounded..