Access quey that finds numbers - conversion to SQL

I have the following query in Access

SELECT dbo_notes.notesid, dbo_notes.jobid, dbo_notes.notes
FROM dbo_notes
GROUP BY dbo_notes.notesid, dbo_notes.jobid, dbo_notes.notes
HAVING (((dbo_notes.notes) Like "######"));

How do I convert it to SQL, in particular the HAVING (((dbo_notes.notes) Like "######")); part

Thanks

P
polynominalAsked:
Who is Participating?
 
mcmonapConnect With a Mentor Commented:
Hi polynominal,

I think you can do this as below,it will find records where there a six numbers next to each other:

SELECT      n.notesid, n.jobid, n.notes
FROM      dbo_notes n
WHERE      n.notes LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%'
0
 
MannaraprayilCommented:
Forthe above query you need not use HAVING because you are not dealing with any aggregate function in select query.

SELECT dbo_notes.notesid, dbo_notes.jobid, dbo_notes.notes
FROM dbo_notes
WHERE dbo_notes.notes ='######'
GROUP BY dbo_notes.notesid, dbo_notes.jobid, dbo_notes.notes

BTW what are you trying to do with  (((dbo_notes.notes) Like "######")) ..?

You can even avoid GROUP BY CLAUSE and use DISTINCT as below

SELECT DISTINCT dbo_notes.notesid, dbo_notes.jobid, dbo_notes.notes
FROM dbo_notes
WHERE dbo_notes.notes ='######'


Thanks
0
 
polynominalAuthor Commented:
Hi, its trying to find a six figure number in a text field
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
MannaraprayilCommented:
If the number is 123456

SELECT DISTINCT dbo_notes.notesid, dbo_notes.jobid, dbo_notes.notes
FROM dbo_notes
WHERE dbo_notes.notes LIKE '%123456%'

0
 
polynominalAuthor Commented:
the number could be any 6 figure number i.e. it could be 654289 or 412514 or whatever
0
 
MannaraprayilCommented:
Then you should pass it as a parameter the use a dynamic query

@numbericValue -- is ur number value
Declare @sqlQuery VARCHAR(500)
SET @sqlQuery = 'SELECT DISTINCT dbo_notes.notesid, dbo_notes.jobid, dbo_notes.notes
FROM dbo_notes WHERE dbo_notes.notes LIKE ''%'+@numbericValue +'%'''

EXEC (@sqlQuery )
0
 
pdrgCommented:
SELECT     n.notesid, n.jobid, n.notes
FROM     dbo_notes n
WHERE     convert(int, n.notes) BETWEEN 100000 and 999999

ought to cover it, or is at least functionally equivelent to your originally posted code.

hth
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.

All Courses

From novice to tech pro — start learning today.