Find a string with two dots

Question

Find a string with two dots

Viewing 8 posts - 16 through 23 (of 23 total)

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

Jeff Moden SSC Guru Points: 1004773 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)

Steve Collins SSCrazy Points: 2275 More actions · Answer 2

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

naumon765 Valued Member Points: 74 More actions · Answer 3

Screenshot 2026-08-10 101837

/*

DROP TABLE dbo.TestData; CREATE TABLE dbo.TestData ( Column1 VARCHAR(50));

INSERT INTO dbo.TestData (Column1) VALUES ('13.4.'),('16.8.7'),('13.4'),('134'),('45.67'),('123');

*/

SELECT * FROM TestData

SELECT

TRY_CAST(Column1 AS NUMERIC(10,2))

FROM

TestData

WHERE

LEN(Column1) - LEN(REPLACE(Column1, '.', '')) <= 1

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

Jeff Moden SSC Guru Points: 1004773 More actions · Answer 4

naumon765 wrote:

Suggestion.... Post graphics is pretty but useless for code. If you want "pretty" code, then use the code block when you create your post. It's not the prettiest but it'll do.

--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 Valued Member Points: 74 More actions · Answer 5

thanks Jeff

for your suggestion

posted the code also

gif = viusal .. nice .. easy to understand

code = play around .... implement etc etc

download

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

Jeff Moden SSC Guru Points: 1004773 More actions · Answer 6

naumon765 wrote:

thanks Jeff for your suggestion posted the code also gif = viusal .. nice .. easy to understand code = play around .... implement etc etc

The code is better but try using the icon at the top of the edit window when you're making/editing a post. Put you code in the code window that shows up.

--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 Valued Member Points: 74 More actions · Answer 7

thanks Jeff

already tried it ages ago

did not like it "bold font huge font"

thumbs up

frederico_fonseca SSCoach Points: 16342 More actions · Answer 8

posting correctly formatted works fine for me

/*
drop table dbo.TestData; create table dbo.TestData ( Column1 varchar(50));
insert into dbo.TestData (Column1) values ('13.4.'),('16.8.7'),('13.4'),('134'),('45.67'),('123');
*/select * from TestData
select try_cast(column1 as numeric(10,2))
from TestData
where len(Column1) - len(replace(Column1, '.', '')) <= 1

This reply was modified 1 weeks, 3 days ago by frederico_fonseca.