January 2, 2018 at 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.
January 2, 2018 at 5:35 am
This was removed by the editor as SPAM
January 2, 2018 at 5:51 am
subramaniam.chandrasekar - Tuesday, January 2, 2018 5:35 AMTry 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?
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
January 2, 2018 at 5:53 am
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
January 2, 2018 at 6:15 am
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.
January 2, 2018 at 7:06 am
Why not try
WHERE Column1 not like '%.%'
this will filter one dot or more
Thanks
January 2, 2018 at 7:15 am
tapasyagupta7 - Tuesday, January 2, 2018 7:06 AMWhy not tryWHERE 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
January 2, 2018 at 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.
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 '%.%.%';
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
January 2, 2018 at 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2018 at 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
...
January 3, 2018 at 6:21 am
HappyGeek - Wednesday, January 3, 2018 1:46 AMExcuse 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.
January 3, 2018 at 6:49 am
Luis Cazares - Wednesday, January 3, 2018 6:21 AMHappyGeek - Wednesday, January 3, 2018 1:46 AMExcuse 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.
...
January 3, 2018 at 7:12 am
drew.allen - Tuesday, January 2, 2018 11:30 AMChrisM@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:
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
January 3, 2018 at 1:42 pm
Thanks for all your help.
January 3, 2018 at 1:47 pm
khpcbgnt - Wednesday, January 3, 2018 1:42 PMThanks 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