SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find a string with two dots


Find a string with two dots

Author
Message
khpcbgnt
khpcbgnt
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 13
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.
subramaniam.chandrasekar
subramaniam.chandrasekar
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 238
khpcbgnt - Tuesday, January 2, 2018 5:06 AM
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.

Try like this,

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

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

Thom A
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46170 Visits: 15686
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
J Livingston SQL
J Livingston SQL
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27412 Visits: 41389
maybe,,,,,,

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

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

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


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

Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96344 Visits: 21215
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
@Taps
@Taps
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 83
Why not try

WHERE Column1 not like '%.%'

this will filter one dot or more

Thanks
Thom A
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46170 Visits: 15686
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97720 Visits: 20711

-- 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
Exploring Recursive CTEs by Example Dwain Camps
drew.allen
drew.allen
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37038 Visits: 13704
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
HappyGeek
HappyGeek
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6927 Visits: 4509
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



...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search