Counting spaces before and after a decimal point

问题:

I have data set as a varchar(500), but I only know if it's numeric or character.

I need to count the max spaces of the length of a column AND the max spaces after a decimal point.

For Example:

ColumnA
1234.56789
123.4567890

would return 11 spaces total AND 7 spaces after the decimal.

It can be two separate queries.

回答1:

SELECT LEN(ColumnA )
      ,CHARINDEX('.',REVERSE(ColumnA ))-1
FROM Table1

If a value has no decimal, the above will return -1 for the spaces after decimal, so you could use:

SELECT LEN(ColumnA)
      ,CASE WHEN ColumnA LIKE '%.%' THEN CHARINDEX('.',REVERSE(ColumnA))-1
            ELSE 0
       END
FROM Table1

Demo of both: SQL Fiddle

If you just wanted the MAX() then you'd just wrap the above in MAX():

SELECT MAX(LEN(ColumnA ))
      ,MAX(CHARINDEX('.',REVERSE(ColumnA ))-1)
FROM Table1

回答2:

SELECT len(ColumnA), len(columnA) - charIndex('.',ColumnA)
FROM theTable

回答3:

SELECT ColumnA, Len(ColumnA) As Total, LEN(SUBSTRING(ColumnA,CHARINDEX('.',ColumnA,LEN(ColumnA)) As Decimal
FROM TABLE