CODEWITHSUNDEEP
sqljdbcintersystems-cachedbeaver
Frantumn
Frantumn

Reputation: 1764

Select all where hashtag isn't working

I have a column that houses varchar. Some of the data in this column read as a hashtag (no spaces on either side of it, I've checked).

When I do a select * without conditions I can see the hashtag data (in screenshot).

enter image description here

When I do a select to count the rows with hashtags, I get a return of 0. Why is the query not able to find

Here are my two queries

-- returns 0
select count(LastVisitNumber) 
from qcpr_ARF_OC.Visit
where LastVisitNumber = '#';

-- returns several rows and some are just #.
select * from qcpr_ARF_OC.Visit;

Upvotes: 1

Views: 308

Answers (3)

user28301763
user28301763

Reputation: 1

I GOT IT! (Had same problem, VERY little help on from web search, but by querying just for # and then for ######, I realized that # is a character used as wildcard by Access to search for digits, so to "escape" the wildcard...)

Enclose your # in square brackets.

Try: where LastVisitNumber = '[#]';

-- returns 0
select count(LastVisitNumber) 
from qcpr_ARF_OC.Visit
where LastVisitNumber = '[#]';

The answer was here with MS all along:

https://support.microsoft.com/en-us/office/use-wildcards-in-queries-and-parameters-in-access-ec057a45-78b1-4d16-8c20-242cde582e0b

Under the heading "Syntax to Retrieve Wildcard Characters From Your Data"

Character: Number sign #
Required Syntax: [#]

:) Happy coding.

Upvotes: 0

TallTed
TallTed

Reputation: 9434

I wonder if the hash requires an escape, even though Caché documentation doesn't seem to say anything about it...

where LastVisitNumber like '%\#%';

If that fails, these 4 alternative queries may help.... Just swap out the ORDER BY clauses.

    SELECT LastVisitNumber, 
           CHAR_LENGTH(LastVisitNumber)
      FROM qcpr_ARF_OC.Visit
     WHERE LastVisitNumber NOT NULL
  ORDER BY LastVisitNumber
# ORDER BY CHAR_LENGTH(LastVisitNumber)
# ORDER BY LastVisitNumber DESC
# ORDER BY CHAR_LENGTH(LastVisitNumber) DESC

You might also try querying through ODBC (instead of JDBC), which makes it easier to generate traces that may reveal surprising details.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

There are probably other characters that you cannot see. I would start with:

select count(LastVisitNumber) 
from qcpr_ARF_OC.Visit
where LastVisitNumber like '%#%';

This assumes that LastVisitNumber is actually a string, despite its name.

Upvotes: 0

Related Questions