Find a string with two dots

Question

Find a string with two dots

Viewing 3 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1004720 More actions · Answer 1

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.

After reading all the posts on this thread, I've not been able to figure out exactly what you want to do.

On one hand, you say that you want to convert the "field" to numeric. On the other hand, you say you want to "bypass" (ignore, according to your WHERE clause) both of the given values (one with a single dot and one with two dots).

What are you really trying to do? Do you actually mean that if it has a dot in the "field" to ignore the row and if it doesn't, convert the "field" to numeric?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

naumon765 SSC Rookie Points: 29 More actions · Answer 2

hi hope this helps

DROP TABLE IF EXISTS SampleData;

CREATE TABLE SampleData

(

col1 NVARCHAR(max)

);

INSERT INTO SampleData

(col1)

select '10.4.'

union all

select '12.1.4'

union all

select '13.789'

union all

select '90.1'

t-sql solution

select * from sampledata where len(col1) - len(replace(col1, '.', '')) = 1

Result

This reply was modified 1 weeks, 5 days ago by naumon765.
This reply was modified 1 weeks, 5 days ago by naumon765.
This reply was modified 1 weeks, 5 days ago by naumon765.
This reply was modified 1 weeks, 5 days ago by naumon765.

Steve Collins SSCrazy Points: 2264 More actions · Answer 3

Jeff's question is really an open issue. As written the requirement isn't clear so what does "not working" mean? The predicate should already exclude values with two or more dots:

WHERE Column1 NOT LIKE '%.%.%'

In terms of language one interpretation of this sentence with [edits] could be:

"I am trying to [apply a] bypass [to] these two strings with the following where clause but it's not working"

If "bypass" means "don't convert the bad values" then maybe the real issue is not finding dots but safely attempting the conversion. Maybe the OP's full query is doing more than shown or there are other non-convertible values. If the goal is to return only values that are actually convertible to the target numeric type I'd test that directly instead of counting dots

select t.string, v.string_as_decimal
from (values ('10.4.'),
             ('12.1.4'),
             ('1'),
             ('13.789'),
             ('90.1')) as t(string)
     cross apply (values (try_cast(t.string as decimal(18, 4)))) as v(string_as_decimal)
where v.string_as_decimal is not null;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können