0

Folks, I am trying to create a query to identify orders that have specific reference number or alpha character _.

This query is returning results, but also is showing some other like results:

SELECT DISTINCT 
    O.ordernumber, R.refnumber 'PO Reference', I.invstatus 'InvoicStatus',
    O.ord_invoicestatus
FROM 
    reference R 
INNER JOIN 
    order O ON R.ordernumber = O.ordernumber
LEFT JOIN 
    invoice I ON I.ordernumber = O.ordernumber
WHERE 
    R.ref_num LIKE '%X_%'
    AND R.reftype = 'PO' 
    AND o.obillto LIKE 'LO%'
ORDER BY 
    2

The highlighted part in the screenshot is what I don't want to find in my result set.

Any suggestions?

Thanks!

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
K S
  • 1
  • 1

2 Answers2

0

Underscore is a wildcard character in TSQL, similar to % but only for one character.

You'll have to escape the underscore, SQL Server Escape an Underscore is similar with some examples.

OwlsSleeping
  • 1,487
  • 2
  • 11
  • 19
0

Change this:

WHERE 
    R.ref_num LIKE '%X_%'

To 

WHERE 
    R.ref_num LIKE '%X[_]%'
Alan Burstein
  • 7,770
  • 1
  • 15
  • 18