Find a string with two dots

  • Hi ,

    I have two values 13.4. and 16.8.7 in a field that is varchar type. I am trying to convert the values in this field to numeric and I am having issue with converting these kind of values which has more than one dot.

    I am trying to bypass these two strings with the following where clause but it's not working/

    WHERE Column1  not like '%.%.%'

    Any ideas?

    Thanks in advance.

  • This was removed by the editor as SPAM

  • 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

  • maybe,,,,,,

    DECLARE @test-2 TABLE (
    coltoconvert varchar(20)
    );

    INSERT INTO @test-2 (coltoconvert )
    VALUES ('123') ,('665584'),('13.4'),('16.8.7')

    SELECT coltoconvert,TRY_CONVERT( INT, coltoconvert)FROM @test-2;
    SELECT coltoconvert,TRY_CONVERT( DECIMAL(18, 2), coltoconvert)FROM @test-2;

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • I wonder if the problem comes from something like this:

    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 '%.%.%'
    AND CONVERT(decimal(2,1),S) > 1 ;

    This is not guaranteed to work every time and it's prone to errors as  t-sql is a declarative language and not a procedural language.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Why not try

    WHERE Column1 not like '%.%'

    this will filter one dot or more

    Thanks

  • tapasyagupta7 - Tuesday, January 2, 2018 7:06 AM

    Why not try

    WHERE Column1 not like '%.%'

    this will filter one dot or more

    Thanks

    Which would, also, filter out any values that have a decimal point. Such as 1.1. Thus, you only end up with the rows that have integer values; this is not what the OP is after.

    Thom~

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

  • -- SQL Server will perform the filter and the conversion in either order

    -- Note that Compute Scalar is plonked anywhere convenient in the execution plan

    -- and may not relate to where it actually occurs, as here.

    WITH SampleData AS (SELECT * FROM (VALUES ('1.2.1'), ('1'), ('1.1'), ('1.1.1')) V (S))

    SELECT

    s1.s,

    NewVal = CASE WHEN s1.s NOT LIKE '%.%.%' THEN CONVERT(decimal(2,1),s1.s) ELSE NULL END

    FROM SampleData s1, SampleData s2, SampleData s3

    WHERE s1.s NOT LIKE '%.%.%' OR s2.s NOT LIKE '%.%.%';

    WITH SampleData AS (SELECT * FROM (VALUES ('1.2.1'), ('1'), ('1.1'), ('1.1.1')) V (S))

    SELECT

    s1.s,

    CONVERT(decimal(2,1),s1.s)

    FROM SampleData s1, SampleData s2, SampleData s3

    WHERE s1.s NOT LIKE '%.%.%' OR s2.s NOT LIKE '%.%.%';

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work - Tuesday, January 2, 2018 10:16 AM

    -- SQL Server will perform the filter and the conversion in either order

    -- Note that Compute Scalar is plonked anywhere convenient in the execution plan

    -- and may not relate to where it actually occurs, as here.

    It was my understanding that the query follows the execution plan, so the Compute Scalar determines exactly where it actually occurs.  I think you meant that it may not be where it logically occurs.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Excuse the formatting, it seems the interface likes it like this!!!! But as an alternative, something like this:-

    CREATE TABLE #testme(ConvertMe varchar(20));

    INSERT INTO #testme (ConvertMe )

    VALUES ('123') ,('665584'),('13.4'),('16.8.7'),('1.2.1'), ('1'), ('1.1'), ('1.1.1'),('10.8.7.5.2')

    SELECT

    CASE

    WHEN CHARINDEX('.', ConvertMe) < 2 THEN CONVERT(DECIMAL(10,1), ConvertMe)

    ELSE CONVERT(DECIMAL(10,1),LEFT(ConvertMe, CHARINDEX('.', ConvertMe) + 1))

    END NewVal

    FROM #TestMe

    DROP TABLE #testme

    ...

  • HappyGeek - Wednesday, January 3, 2018 1:46 AM

    Excuse the formatting, it seems the interface likes it like this!!!! But as an alternative, something like this:-

    CREATE TABLE #testme(ConvertMe varchar(20));

    INSERT INTO #testme (ConvertMe )

    VALUES ('123') ,('665584'),('13.4'),('16.8.7'),('1.2.1'), ('1'), ('1.1'), ('1.1.1'),('10.8.7.5.2')

    SELECT

    CASE

    WHEN CHARINDEX('.', ConvertMe) < 2 THEN CONVERT(DECIMAL(10,1), ConvertMe)

    ELSE CONVERT(DECIMAL(10,1),LEFT(ConvertMe, CHARINDEX('.', ConvertMe) + 1))

    END NewVal

    FROM #TestMe

    DROP TABLE #testme

    I'm not sure about that. You seem to be checking for position of the dot instead of the number of them and then truncating the values to convert.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Wednesday, January 3, 2018 6:21 AM

    HappyGeek - Wednesday, January 3, 2018 1:46 AM

    Excuse the formatting, it seems the interface likes it like this!!!! But as an alternative, something like this:-

    CREATE TABLE #testme(ConvertMe varchar(20));

    INSERT INTO #testme (ConvertMe )

    VALUES ('123') ,('665584'),('13.4'),('16.8.7'),('1.2.1'), ('1'), ('1.1'), ('1.1.1'),('10.8.7.5.2')

    SELECT

    CASE

    WHEN CHARINDEX('.', ConvertMe) < 2 THEN CONVERT(DECIMAL(10,1), ConvertMe)

    ELSE CONVERT(DECIMAL(10,1),LEFT(ConvertMe, CHARINDEX('.', ConvertMe) + 1))

    END NewVal

    FROM #TestMe

    DROP TABLE #testme

    I'm not sure about that. You seem to be checking for position of the dot instead of the number of them and then truncating the values to convert.

    True but the OP said he was trying to convert the values in the string and has not answered the question about how anything after the second dot should be treated or actually confirmed they should be ignored completely, so it seemed just as likely that anything after the second dot will be ignored in the final solution. I admit not ideal.

    ...

  • drew.allen - Tuesday, January 2, 2018 11:30 AM

    ChrisM@Work - Tuesday, January 2, 2018 10:16 AM

    -- SQL Server will perform the filter and the conversion in either order

    -- Note that Compute Scalar is plonked anywhere convenient in the execution plan

    -- and may not relate to where it actually occurs, as here.

    It was my understanding that the query follows the execution plan, so the Compute Scalar determines exactly where it actually occurs.  I think you meant that it may not be where it logically occurs.

    Drew

    Unless I can find the original reference - a PW article I'm fairly sure - I might have to retract that, Drew. I've found one reference which comes close but not quite, stating that the actual expression evaluation can occur almost anywhere to the left of the Compute Scalar placeholder:

    http://sqlblog.com/blogs/paul_white/archive/2012/09/05/compute-scalars-expressions-and-execution-plan-performance.aspx

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks for all your help.

  • khpcbgnt - Wednesday, January 3, 2018 1:42 PM

    Thanks for all your help.

    ....and what solution have you decided to use?....tis a two way street here ๐Ÿ™‚

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply