• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 63
  • Last Modified:

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?
0
sbornstein2
Asked:
sbornstein2
  • 9
  • 3
  • 2
  • +1
1 Solution
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
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 PrewCommented:
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 PrewCommented:
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 PrewCommented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 9
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now