I'm using mssql 2012 version

Example value

     col
0  '-1.1 1'
1  '-1.0-1'
2  '1.1 2'
3  '1.1-2'

I want this Result

     col
0    -1.1
1    -1.0
2    1.1
3    1.1

if table name is 'tb' and column name is 'col' how can get the result?

I'm tried Try_convert() but can't reach the answer

CodePudding user response：

reverse the string and then use patindex to find the or - symbol. Then use left() to extract the required portion

left(col, len(col) - patindex('%[ -]%', reverse(col)))

CodePudding user response：

You can use SUBSTRING with reverse function.

select reverse(substring(reverse(col),3,len(col))) as modified_col 
from (values ('1.0 1'),('-1.1-1')) as t(col)

DB fiddle