SQL Query Question string contains

Hello all,

I need to write a query that returns all rows that a field does not format to int.int.int i.e. (10.11.44).

Any suggestions best way to handle that?
sbornstein2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try this -

You need to use PATINDEX.

SELECT * FROM YourTable
WHERE PatIndex('%[0-9]%',REPLACE(YourcolumnName,'.','')) = 0

Open in new window


refer more - https://docs.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql
0
sbornstein2Author Commented:
Did not seem to work
0
Pawan KumarDatabase ExpertCommented:
Yes got that. working on a another idea.
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Pawan KumarDatabase ExpertCommented:
Please try this

Full tested solution

CREATE TABLE tst1
(
	 ID INT
	,Val VARCHAR(10)
)
GO


INSERT INTO tst1 VALUES (1,'10.11.44') , (2,'1.1'), (3,'2.3.1')
GO
INSERT INTO tst1 VALUES (4,'a.b.c')

GO

SELECT Id,Val 
FROM 
(

SELECT * , 
 SUBSTRING( Val, 0 , CHARINDEX ( '.' , Val , 0 ) ) a
,SUBSTRING(Val, CHARINDEX ( '.' , Val , 0 )+1 , CHARINDEX ( '.', Val ,CHARINDEX ( '.' , Val , 0 )  )-1  ) b
,SUBSTRING(Val, CHARINDEX ( '.', Val ,CHARINDEX ( '.' , Val , 0 ))+ LEN(SUBSTRING(Val, CHARINDEX ( '.' , Val , 0 )+1 
	, CHARINDEX ( '.', Val ,CHARINDEX ( '.' , Val , 0 )  )-1  ))+2, LEN(Val) ) c
FROM tst1

)t 
WHERE a <> '' AND b <> '' and c <> ''
AND PatIndex('%[0-9]%',a) > 0
AND PatIndex('%[0-9]%',b) > 0
AND PatIndex('%[0-9]%',c) > 0

Open in new window


Solution

SELECT Id,Val 
FROM 
(

SELECT * , 
 SUBSTRING( Val, 0 , CHARINDEX ( '.' , Val , 0 ) ) a
,SUBSTRING(Val, CHARINDEX ( '.' , Val , 0 )+1 , CHARINDEX ( '.', Val ,CHARINDEX ( '.' , Val , 0 )  )-1  ) b
,SUBSTRING(Val, CHARINDEX ( '.', Val ,CHARINDEX ( '.' , Val , 0 ))+ LEN(SUBSTRING(Val, CHARINDEX ( '.' , Val , 0 )+1 
	, CHARINDEX ( '.', Val ,CHARINDEX ( '.' , Val , 0 )  )-1  ))+2, LEN(Val) ) c
FROM tst1

)t 
WHERE a <> '' AND b <> '' and c <> ''
AND PatIndex('%[0-9]%',a) > 0
AND PatIndex('%[0-9]%',b) > 0
AND PatIndex('%[0-9]%',c) > 0

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
Id          Val
----------- ----------
1           10.11.44
3           2.3.1

(2 row(s) affected)

Open in new window

0
Olaf DoschkeSoftware DeveloperCommented:
I would handle it this way:

Declare @test As Table(ID int, ipadr Varchar(10), rating Varchar(50));

INSERT INTO @test VALUES (1,'10.11.44','OK')
   ,                     (2,'1.1','not enough points')
   ,                     (3,'2.3.1','OK')
   ,                     (4,'a.b.c','letters instead of numbers')
   ,                     (5,'12..78','wrong - two points');

-- select OK records
Select * from @test WHERE (isnumeric( Replace(ipadr,'.','') )=1 AND LEN(Replace(ipadr,'.','')) = LEN(ipadr)-2 AND ipadr LIKE '_%._%._%')

-- select NOT OK records
Select * from @test WHERE NOT (isnumeric( Replace(ipadr,'.','') )=1 AND LEN(Replace(ipadr,'.','')) = LEN(ipadr)-2 AND ipadr LIKE '_%._%._%')

Open in new window


PS: If this was about IP addresses, you'd check for three points, anyway, this could be about three-part chapter numbers or anything, so whatever your column name is instead of ipadr...

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
@Olaf

Your code will NOT work for below entry

(8,'$.1.1','Ok')

Isnumeric is not a dependable function.
0
Pawan KumarDatabase ExpertCommented:
@Author -

Please try this ..EASY and Cool

Declare @test As Table(ID int, ipadr Varchar(10), rating Varchar(50));

INSERT INTO @test VALUES (1,'10.11.44','OK')
   ,                     (2,'1.1','not enough points')
   ,                     (3,'2.3.1','OK')
   ,                     (4,'a.b.c','letters instead of numbers')
   ,                     (5,'12..78','wrong - two points')
   ,					 (8,'$.1.1','Ok')

Select * from @test WHERE ipadr LIKE '[0-9]%.[0-9]%.[0-9]%'

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/

(6 row(s) affected)
ID          ipadr      rating
----------- ---------- --------------------------------------------------
1           10.11.44   OK
3           2.3.1      OK

(2 row(s) affected)

Open in new window

0
Bill PrewIT / Software Engineering ConsultantCommented:
What about these, they shouldn't pass...

   ,                               (9,'1aaa.2bbb.3ccc','bad, letters')
   ,                               (10,'1.2.3.4','bad, too many periods')


»bp
0
Pawan KumarDatabase ExpertCommented:
No.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Right, but they do...
0
Pawan KumarDatabase ExpertCommented:
Bad , We need few more conditions..

I think below will handle all the different cases.

/*------------------------
Declare @test As Table(ID int, ipadr Varchar(100), rating Varchar(50));

INSERT INTO @test VALUES (1,'10.11.44','OK')
   ,                     (2,'1.1','not enough points')
   ,                     (3,'2.3.1','OK')
   ,                     (4,'a.b.c','letters instead of numbers')
   ,                     (5,'12..78','wrong - two points')
   ,			 (8,'$.1.1','Ok')
   ,                     (9,'1aaa.2bbb.3ccc','bad, letters')
   ,                     (10,'1.2.3.4','bad, too many periods')


Select * from @test WHERE ipadr LIKE '[0-9]%.[0-9]%.[0-9]%'
AND TRY_CAST( REPLACE(ipadr,'.','') AS BIGINT ) IS NOT NULL AND DATALENGTH(ipadr)  - DATALENGTH(REPLACE(ipadr,'.','')) = 2
------------------------*/

(8 row(s) affected)
ID          ipadr                                                                                                rating
----------- ---------------------------------------------------------------------------------------------------- --------------------------------------------------
1           10.11.44                                                                                             OK
3           2.3.1                                                                                                OK

(2 row(s) affected)

Open in new window

0
Bill PrewIT / Software Engineering ConsultantCommented:
@sbornstein2,

  1. So, acceptable values must have the form a.b.c where a, b and c are integer numbers?
  2. Are there limits on the numbers or can they contain an unlimited number of digits?
  3. Is 0 valid by itself?
  4. Is - valid (a negative number) or must they be positive?


»bp
0
sbornstein2Author Commented:
Worked exactly as needed and simple.  Thanks
0
Pawan KumarDatabase ExpertCommented:
@sbornstein2

Why are you selecting a solution which is going to fail ? I have already explained you how it will fail.   This will not help future reads, would request you to reconsider.

Also I have solved it first for you
0
Olaf DoschkeSoftware DeveloperCommented:
Pawan Kumar,

It's really simple to also check for $ and exclude that, so your complaint is easy to fix, eg AND NOT field LIKE '%$%'

Bye, Olaf.
0
Pawan KumarDatabase ExpertCommented:
Olaf

How many where clause will you add. Your code will fail for below also.

ID      ipadr      rating
2      ,.1.2      am I ok
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.