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 (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1688 Visits: 492
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 Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53457 Visits: 16666
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
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32548 Visits: 41761
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 (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115989 Visits: 21708
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
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 232
Why not try

WHERE Column1 not like '%.%'

this will filter one dot or more

Thanks
Thom A
Thom A
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53457 Visits: 16666
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 (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117974 Visits: 21101

-- 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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45077 Visits: 14686
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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8954 Visits: 5675
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