HELP NEEDED with Removing information after a decimal in a text field

Question

Post reply

HELP NEEDED with Removing information after a decimal in a text field

Susan Campbell

Newbie

Points: 4
More actions
March 1, 2007 at 7:31 pm

#172617

I have a data base where I need to remove the data in a text field that is after the decimal. Can someone help me with a SQL Script exa!mple?
Thanks!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Susan

You'll have to help us out a bit more than that, please - how about an example?

Thanks

John

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 2

Somthing along the lines of

UPDATE

tableName SET data = CASE WHEN PATINDEX('%.%', data) = 0 THEN data ELSE SUBSTRING(data, 0, PATINDEX('%.%', data)) END;

Might do the trick if I have understood you correctly. (or it might just trash your data if I haven't )

- James

--
James Moore
Red Gate Software Ltd

Plateau SSC Veteran Points: 268 More actions · Answer 3

Try this in your test database first

UPDATE

[table1]

SET

[field1] =

CASE

WHEN PATINDEX ('%.%', [field1]) > 1 THEN SUBSTRING ([field1], PATINDEX ('%.%', [field1]) - len([field1]), len([field1]))

ELSE [field1]

END

Susan Campbell Newbie Points: 4 More actions · Answer 4

Thanks all for the suggestions...I will give them a try.

I thought it woul be similar to the following. However it does not like “InStr”

UPDATE basic

SET alternate_id = Left(alternate_id,InStr(1,alternate_id,'.')-1)

WHERE alternate_id <> Null

The basic concept is I have a text field with a number in it like 2345.123 and I need to delete the .123 and leave the 2345.

Thanks for all your help.

Plateau SSC Veteran Points: 268 More actions · Answer 5

Instr is not a function in SQL, you need to use Padindex.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Susan

If all the data in your column is decimals, you can convert to a number and take the integer part:

SELECT FLOOR(CAST('2345.123' AS DECIMAL))

John

Susan Campbell Newbie Points: 4 More actions · Answer 7

Thanks for the help.

Here is the script I used..

UPDATE basic

SET alternate_id = CASE

WHEN PATINDEX ('%.%', alternate_id) > 1 THEN SUBSTRING (alternate_id, PATINDEX ('%.%', alternate_id) - LENGTH(alternate_id), lenGTH(alternate_id))

ELSE alternate_id

END

The original data was like 1.00 what I wanted was to end up with 1 and what I got was 0.00.

What do I need to h

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

UPDATE basic

SET alternate_id = FLOOR(CAST(alternate_id AS DECIMAL))

Susan Campbell Newbie Points: 4 More actions · Answer 9

Susan Campbell

Newbie

Points: 4

March 2, 2007 at 7:27 am

#692836

John,

Thanks! That did it!

Sue

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Susan

Beware if you have any negative numbers in that column. For example, -1.5 will be converted to -2. If that isn't what you want, you need to put a bit of extra logic in the query.

John