Remove all trailing decimal points from a number stored as a string-CodePudding

I have a couple of strings (nvarchar data type), one is a whole number and one has decimal points trailing. My goal is to remove decimals and have all values as a whole number.

I tried the code below but it gives me an error for the value with no decimals. Is there a way to accomplish this without a case expression. I'll be using this new column in a join.

SELECT [SOW]
  --,LEFT([SOW], CHARINDEX('.', [SOW])-1) as 'TestColumn'
FROM [dbo].[t_Schedule_kdm]
WHERE sow  in ('15229.11','11092')

Output:
11092
15229.11

My desired Output:
11092
15229

CodePudding user response：

Just append a dot character so that you'll always find an index:

LEFT(SOW, CHARINDEX('.', SOW   '.') - 1)

It's not clear whether you need to cast the result of that expression to an integer value.

CodePudding user response：

Convert first to the most precision number you could ever have e.g. decimal(9,2) then convert to an int. You can't convert directly from a decimal string to an int.

SELECT [Value]
  , CONVERT(int,CONVERT(decimal(9,2),[Value]))
FROM (
    VALUES ('15229.11'),('11092')
) x ([Value]);