• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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
0
polynominal
Asked:
polynominal
1 Solution
 
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
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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
 
mcmonapCommented:
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now