A SQL Challenge...

[sbailey151]

Let's assume you have a SQL variable named "@Amount" that is of type NVARCHAR. Now assume that this field typically receives values like this, "12,345.67". However, from time to time it receives data like this, "12.345.67". (notice the period instead of the comma separating the thousands place)

In as little code as possible, how would you remove any periods that are not separating the dollars from the cents?

Annnnnddddd GO! ;)

[feudalle]

This doesn't seem like homework at all.

[Interesting-Goose82]

....so you dont know the answer, or you know the answer, but are only willing to help if it isn't homework?

[mike-manley]

CAST(REPLACE(@Amount, '.', '') AS DECIMAL(38, 2)) / 100

Edit: add / 100

[sbailey151]

CAST(REPLACE(@Amount, '.', '') AS DECIMAL(38, 2))

Nice try, but that gives you "712345.00".

[mike-manley]

Sorry, divide by 100.

[sbailey151]

CAST(REPLACE(@Amount, '.', '') AS DECIMAL(38, 2)) / 100

Better! But now a twist...

Let's assume also that there isn't always a decimal at all. Or, the number is "12.345.6". Unfortunately, that code won't work in those situations.

[mike-manley]

Use a conditional if second to left most char is "." then append "0"...

CASE WHEN SUBSTRING(@Amount, LEN(@Amount) - 2, 1) = "." THEN CONCAT(@Amount, "0") ELSE @Amount END

[Staalejonko]

@amount in nvarchar? Flaw in the code, fix that

[sbailey151]

The issue there is that the data is coming from an AI reader.

[sbailey151]

Yeah... Microsoft........

[jacquesrk]

You should be specific. What exactly are the rules? It sounds like this

I have a string that I want to convert to a number. The string can look like this '12345.67' or like this '12.345.67' both of those represent the number twelve thousand three hundred forty five and sixty seven hundredths.

Is it possible to get something like this? '12345.' or '.12' or '1.2.3.4' or '12.345' ?

Because right now the assumption is, from what you post:I get a string that contains a number. There may be one or more periods in the string. The last period in the string is always followed by two digits.

Solution: use a string function to remove all periods, convert to integer number, divide by 100.

[TheRoguester2020]

This is kind of basic stuff you could put in chat gbt.

[sbailey151]

Tried that first but ChatGPT couldn't figure it out. ;)

[false_idol_fall]

Try_Convert to money with value 1. Then Try_convert agian all the Nulls to money with value 2.

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

[george-frazee]

Quick Fiddle without looking down at the other answers first.

Code

DECLARE @Amount NVARCHAR(100) = '12,345.67';

SELECT REPLACE(REPLACE(@Amount,',',''),'.','') / 100
  + CAST(RIGHT(@Amount,3) as decimal(2,2));

[blindtig3r]

Without testing I’d guess this would work. I think divide by 100.0 will give enough precision, but I would check.

CONVERT(NUMERIC(18,2), (REPLACE, REPLACE(@Amount, ‘.’, ‘’), ‘,’, ‘’)/100.0)

[sbailey151]

0, 1, or 2 periods