Return 1.23 from 1.2.3 in SQL?

Question

I want to get the value 1.23 from 1.2.3.Similar, I also get the value 1.2.3...x from 1.2.3...x .How can I do this in SQL?

After I get number from string in this topic: How to get number from String sql

Now, this value is 1.2.3. When i cast, sql notice an error: "Error converting data type nvarchar to float" And i want to return the value 1.23 from 1.2.3

You need to provide more data and examples of what you tried so far. — ScarletMerlin, Apr 16 '15 at 02:49
@ScarletMerlin: Mmm, not really. There's enough here to answer the question just fine, whether or not the poster put much effort into it. — Nathan Tuggy, Apr 16 '15 at 02:53
@NathanTuggy I know you can build a simple query to format a string that would provide the desired result. The issue is that we have zero context for the problem. — ScarletMerlin, Apr 16 '15 at 02:55

score 0 · Answer 1 · answered Apr 16 '15 at 03:03

0

You can use an ugly combination of SUBSTRING, REPLACE and PATINDEX. Something like

SELECT
     SUBSTRING(a, 1, PATINDEX('%.%', a) - 1) AS pre
    ,REPLACE(SUBSTRING(a, PATINDEX('%.%', a), LEN(a)), '.', '') AS post
    ,SUBSTRING(a, 1, PATINDEX('%.%', a) - 1) + '.' + REPLACE(SUBSTRING(a, PATINDEX('%.%', a), LEN(a)), '.', '') AS result
FROM (
    SELECT
        '1.2.3' AS a
    ) I

The pre and post columns are the two halves, appended them with a '.' in the result column.

The number before the first '.' will remain before, while any numbers after the the first '.' will be put together after the '.' e.g. 432.546.3.132.6 = 432.54631326

answered Apr 16 '15 at 03:03

Tsarin

171
4

if a is 1, SQL will notice error: "Invalid length parameter passed to the LEFT or SUBSTRING function" – Bader Apr 16 '15 at 03:50
CASE WHEN LEN(a) > 1 THEN ... ELSE a END – Tsarin Apr 16 '15 at 04:17
if 'a' is an interger value, the value doesn't contain '.', Query will be error – Bader Apr 16 '15 at 04:22
If it is an integer it won't contain . at all, let alone more than 1. The error "Error converting data type nvarchar to float" means you are doing this to an nvarchar field – Tsarin Apr 16 '15 at 04:25
If it is an integer it won't contain . at all, let alone more than 1. The error "Invalid length parameter passed to the LEFT or SUBSTRING function". i'm executing your query – Bader Apr 16 '15 at 04:42

score 0 · Accepted Answer · answered Apr 16 '15 at 05:15

you can also use a combination of STUFF and REPLACE and CHARINDEX to achieve what you require. The solution saves the first location of . , replaces all the other decimal points and then stuffs it back in. Something like this

;WITH CTE AS
(
    SELECT '1.23' as CharCol
    UNION ALL SELECT  '6.7.1.'
    UNION ALL SELECT  '4.2.3'
    UNION ALL SELECT  '1.46'
    UNION ALL SELECT  '5.43'
    UNION ALL SELECT  '90'
)
SELECT
    CASE WHEN CHARINDEX('.',CharCol) = 0
        THEN CharCol
        ELSE STUFF(REPLACE(CharCol,'.',''),CHARINDEX('.',CharCol),0,'.')
    END
FROM CTE;

Return 1.23 from 1.2.3 in SQL?

2 Answers2