Home Forums SQL Server 2017 SQL Server 2017 - Development Find a string with two dots RE: Find a string with two dots

  • January 2, 2018 at 5:51 am

    #1974046

    subramaniam.chandrasekar - Tuesday, January 2, 2018 5:35 AM

    Try like this,

    WHEREColumn1 LIKE '%'+'.'+'%'

    The ones with . will get filtered, Once filtered please cast it to numeric.

    That would return values with 1 or more decimal point, the OP is trying to filter out those with more than 1.

    NOT LIKE '%.%.%' should actually work (consider the below SQL), so I think we need more information here. Define "not working". Are you getting an error, unexpected results, something else?

    WITH VTE AS(
      SELECT *
      FROM (VALUES('1'),('1.1'),('1.1.1')) V(S))
    SELECT CONVERT(decimal(2,1),S)
    FROM VTE
    WHERE S NOT LIKE '%.%.%';

    This returns 1.0 and 1.1.

    Also, khpcbgnt, what does the value 16.8.7 represent? 16.87? 168.7? Other? If we know how to treat more than one decimal point, we can help you CONVERT those too.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk