Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

LIKE comparism not working

Status
Not open for further replies.
Karl Blessing

Karl Blessing

Programmer
Feb 25, 2000
2,936
US
when I do a direct comparism such as

"B4 520301"

it returns a record, howerver if I use

LIKE "B4%"

I do not get any records back

I want to be able to do this

"_4%" (the # will be changed by ASP )

but first I must figure out why the like clause isnt working.

Karl
kb244@kb244.com
Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

 
Sort by date Sort by votes
here is a SQL String generated by access, if it helps you help me

Code: 
SELECT 
Sum(CgDetYear.Completers) AS Completers, 
Sum(CgDetYear.WageEarners) AS WageEarners, 
Sum(CgDetYear.Wage14999) AS Wage14999, 
Sum(CgDetYear.Wage24999) AS Wage24999, 
Sum(CgDetYear.Wage49999) AS Wage49999, 
Sum(CgDetYear.Wage50000) AS Wage50000, 
Sum(CgDetYear.Military) AS Military, 
CgDetYear.Year
FROM CgDetYear
GROUP BY 
CgDetYear.Year, 
CgDetYear.PgmName, 
CgDetYear.key_year, 
CgDetYear.Agency, 
CgDetYear.Program
HAVING 
(((CgDetYear.PgmName)='ACCOUNTING               ') AND
((CgDetYear.key_year)='1997') AND 
((CgDetYear.Agency)='UNC') AND 
((CgDetYear.Program)="B4 520301"));
[tt]
[red] I want to do ((CgDetYear.Program) LIKE "_4%")[/red]
It has been parsed out a bit above for easier reading

Karl
kb244@kb244.com
Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

 
Upvote 0 Downvote
In order to return all records starting with "B4", replace the B4% with B4*.
 
Upvote 0 Downvote
ok if * is any character from zero characters and beyound, what is the wildcard for a single character, like I know the # is ALWAYS going to be in the second place.

Karl
kb244@kb244.com
Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

 
Upvote 0 Downvote
kb244,

just "look up" "Like" in Ms. Access help. It shows all of the "pattern" matching codes w/ examples.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
 
Upvote 0 Downvote
um, Michael thats what I did, and as you can see above, and yet none of those worked, apparently % works in ASP using ADO, but doesnt work when in Access, * works in Access but not ADO under ASP, also _ doesnt seem to have any affect, and if you think I'm not reading the help file, you are wrong.

Karl
kb244@kb244.com
Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

 
Upvote 0 Downvote
from the MSDN Library

Code: 
LIKE (T-SQL)
Determines whether or not a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string; wildcard characters, however, can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any of the arguments are not of character string data type, Microsoft® SQL Server™ converts them to character string data type, if possible.

Syntax
match_expression [NOT] LIKE pattern [ESCAPE escape_character]

Arguments
match_expression 
Is any valid SQL Server expression of character string data type. 
pattern 
Is the pattern to search for in match_expression, and can include these valid SQL Server wildcard characters. 
 

Wildcard character Description Example 
% Any string of zero or more characters. WHERE title LIKE ‘%computer%’ finds all book titles with the word ‘computer’ anywhere in the book title. 
_ (underscore) Any single character. WHERE au_fname LIKE ‘_ean’ finds all four-letter first names that end with ean (Dean, Sean, and so on). 
[ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE ‘[C-P]arsen’ finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. 
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE au_lname LIKE ‘de[^l]%’ all author last names beginning with de and where the following letter is not l. 


escape_character 
Is any valid SQL Server expression of any of the data types of the character string data type category. escape_character has no default and must consist of only one character. 
Result Types
Boolean

Result Value
LIKE returns TRUE if the match_expression matches the specified pattern.

Remarks
When you perform string comparisons with LIKE, all characters in the pattern string are significant, including leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE ‘abc ‘ (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE ‘abc’ (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.

A string comparison using a pattern containing char and varchar data may not pass a LIKE comparison because of how the data is stored. It is important to understand the storage for each data type and where a LIKE comparison may fail. The following example passes a local char variable to a stored procedure and then uses pattern matching to find all of the books by a certain author. In this procedure, the author’s last name is passed as a variable.

CREATE PROCEDURE find_books @AU_LNAME char(20)

AS

SELECT @AU_LNAME = RTRIM(@AU_LNAME) + '%'

SELECT t.title_id, t.title 

FROM authors a, titleauthor ta, titles t

WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id

    AND a.au_lname LIKE @AU_LNAME

  

In the find_books procedure, no rows are returned because the char variable (@AU_LNAME) contains trailing blanks whenever the name contains fewer than 20 characters. Because the au_lname column is varchar, there are no trailing blanks. This procedure fails because the trailing blanks are significant. 

However, this example succeeds because trailing blanks are not added to a varchar variable:

USE pubs

GO

CREATE PROCEDURE find_books2 @au_lname varchar(20)

AS

SELECT t.title_id, t.title 

FROM authors a, titleauthor ta, titles t

WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id

    AND a.au_lname LIKE @au_lname + '%'

  

EXEC find_books2 'ring'

  

Here is the result set:

title_id title                                                                            

-------- ---------------------------------------------------------------

MC3021   The Gourmet Microwave                                                            

PS2091   Is Anger the Enemy?                                                              

PS2091   Is Anger the Enemy?                                                              

PS2106   Life Without Fear                                                                

  

(4 row(s) affected)

  

Pattern Matching with LIKE
It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. However, the clause WHERE arrival_time LIKE ‘%9:20%’ does find it.

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the SQL-92 standard. ASCII LIKE is compatible with earlier versions of SQL Server.

Here is a series of examples that show the differences in rows returned between ASCII and Unicode LIKE pattern matching:

-- ASCII pattern matching with char column

CREATE TABLE t (col1 char(30))

INSERT INTO t VALUES ('Robert King')

SELECT * 

FROM t 

WHERE col1 LIKE '% King'    -- returns 1 row

  

-- Unicode pattern matching with nchar column

CREATE TABLE t (col1 nchar(30))

INSERT INTO t VALUES ('Robert King')

SELECT * 

FROM t 

WHERE col1 LIKE '% King'    -- no rows returned

  

-- Unicode pattern matching with nchar column and RTRIM

CREATE TABLE t (col1 nchar (30))

INSERT INTO t VALUES ('Robert King')

SELECT * 

FROM t 

WHERE RTRIM(col1) LIKE '% King'    -- returns 1 row

  


--------------------------------------------------------------------------------

Note When you perform string comparisons with LIKE, all characters in the pattern string are significant, including every leading or trailing blank (space).


--------------------------------------------------------------------------------

Using the % Wildcard Character
If the LIKE ‘5%’ symbol is specified, SQL Server searches for the number 5 followed by any string of zero or more characters. 

For example, this query shows all system tables in a database, because they all begin with the letters sys:

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME LIKE 'sys%'

  


--------------------------------------------------------------------------------

Note Be aware that system tables can change from version to version. It is recommended that you use the Information Schema Views or applicable stored procedures to work with SQL Server system tables.


--------------------------------------------------------------------------------

To see all objects that are not system tables, use NOT LIKE ‘sys%’. If you have a total of 32 objects and LIKE finds 13 names that match the pattern, NOT LIKE finds the 19 objects that do not match the LIKE pattern.

You may not always find the same names with a pattern such as LIKE ‘[^s][^y][^s]%’. Instead of 19 names, you may get only 14, with all the names that begin with s or have y as the second letter or have s as the third letter eliminated from the results as well as the system table names. This is because match strings with negative wildcards are evaluated in steps, one wildcard at a time. If the match fails at any point in the evaluation, it is eliminated.

Using Wildcard Characters as Literals
You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

Symbol Meaning 
LIKE ‘'5[%]’ 5% 
LIKE ‘[_]n’ _n 
LIKE ‘[a-cdf]’ a, b, c, d, or f 
LIKE ‘[-acdf]’ -, a, c, d, or f 
LIKE ‘[ [ ]’ [ 
LIKE ‘]’ ] 
LIKE ‘abc[_]d%’ abc_d and abc_de 
LIKE ‘abc[def]’ abcd, abce, and abcf 


Pattern Matching with the ESCAPE Clause
You can search for character strings that include one or more of the special wildcard characters. For example, the discounts table in the customers database may store discount values that include a percent sign (%). To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. For example, a sample database contains a column named comment that contains the text 30%. To search for any rows containing the string 30% anywhere in the comment column, specify a WHERE clause of WHERE comment LIKE ‘%30!%%’ ESCAPE ‘!’. Unless ESCAPE and the escape character are specified, SQL Server returns any rows with the string 30.

This example shows how to search for the string “50% off when 100 or more copies are purchased” in the notes column of the titles table in the pubs database:

USE pubs

GO

SELECT notes

FROM titles

WHERE notes LIKE '50%% off when 100 or more copies are purchased' 

    ESCAPE '%'

GO

  

Examples
A. Use LIKE with the % wildcard character
This example finds all phone numbers that have area code 415 in the authors table.

USE pubs

GO

SELECT phone

FROM authors

WHERE phone LIKE '415%'

ORDER by au_lname

GO

  

Here is the result set:

phone        

------------ 

415 658-9932 

415 548-7723 

415 836-7128 

415 986-7020 

415 836-7128 

415 534-9219 

415 585-4620 

415 354-7128 

415 834-2919 

415 843-2991 

415 935-4228 

  

(11 row(s) affected)

  

B. Use NOT LIKE with the % wildcard character
This example finds all phone numbers in the authors table that have area codes other than 415.

USE pubs

GO

SELECT phone

FROM authors

WHERE phone NOT LIKE '415%'

ORDER BY au_lname

GO

  

Here is the result set:

phone        

------------ 

503 745-6402 

219 547-9982 

615 996-8275 

615 297-2723 

707 938-6445 

707 448-4982 

408 286-2428 

301 946-8853 

801 826-0752 

801 826-0752 

913 843-0462 

408 496-7223 

  

(12 row(s) affected)

  

C. Use the ESCAPE clause
This example uses the ESCAPE clause and the escape character to find the exact character string 10-15% in column c1 of the mytbl2 table.

USE pubs

GO

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

        WHERE TABLE_NAME = 'mytbl2')

    DROP TABLE mytbl2

GO

USE pubs

GO

CREATE TABLE mytbl2

(

 c1 sysname

)

GO

INSERT mytbl2 VALUES ('Discount is 10-15% off')

INSERT mytbl2 VALUES ('Discount is .10-.15 off')

GO

SELECT c1 

FROM mytbl2

WHERE c1 LIKE '%10-15!% off%' ESCAPE '!'

GO

  

D. Use the [ ] wildcard characters
This example finds authors with the first name of with Cheryl or Sheryl.

USE pubs

GO

SELECT au_lname, au_fname, phone

FROM authors

WHERE au_fname LIKE '[CS]heryl'

ORDER BY au_lname ASC, au_fname ASC

GO

  

This example finds the rows for authors with last names of Carson, Carsen, Karson, or Karsen.

USE pubs

GO

SELECT au_lname, au_fname, phone

FROM authors

WHERE au_lname LIKE '[CK]ars[eo]n'

ORDER BY au_lname ASC, au_fname ASC

GO[code]

[COLOR=blue] as you can see * was never mentioned [/color] 

Karl
kb244@kb244.com
[URL unfurl="true"]http://kb244.com[/URL]
Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

[URL unfurl="true"]http://www.brainbench.com/transcript.jsp?pid=629151[/URL]
 
Upvote 0 Downvote
Karl,

I wasn't 'casting aspersions' ..., just attempting to "point" to the answer w/o the 'exhaustive' reitteration of what i presume you already have handy. Good illistration of doing the right thing in the wrong space? Ms Access HELP does reference "*"!



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
 
Upvote 0 Downvote
I have to use my MSDN Library, as my seperate MS Access Help files wont start due to "not enough memory" but I would think that MSDN would cover much more, considering when my company pays 2500$ a year for the MSDN subscriptions.

Karl
kb244@kb244.com
Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

 
Upvote 0 Downvote
Shades of 1984, (more is less?). there is indeed MUCH more in your dump of the help - at least in terms of character count. The Ms. Access help on the subject is ~ 25 lines - and half of those are blank. There is 'less' intelligence if you need help w/ Ms. Access - but get help on something else.

Again, I love/hate Ms. It provides ample opportunity for employment with the frustration of not quite getting it together!

Not to belabor the point, but your post is in the Ms. Access forum, while the Help info you posted does refer to "T-SQL". As an "oh-by-the-way", the single character is "?".

Whatever you're running, your employer should find some way for you to get the help for the app you are actually using available to you.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
 
Upvote 0 Downvote
Does this include purchasing and installing hardware? More (MUCH more) memory - or just go for broke - Get a 'new machine' equipped for the task(s) assigned.!!!!

Rember -

"Its better to beg for forgivness than to ask for permission"



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
 
Upvote 0 Downvote
all machines have a minimal of 256 Megs or Rams, mine in particular has 384, I am responsible for the hardware and software at the office, the problem clearly lies in Access 97, since no other application complains, and we do heavy development at the office (however I am new to SQL Queries and what not, but in general I am a good programmer)

Karl
kb244@kb244.com
Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TrekBiker
  • Locked
  • Question
Records in one table not in another 1
Replies
3
Views
146
Andrzejek
Andrzejek
datajockey123
  • Locked
  • Question
Note / Information box with date and time stamp for each entery
Replies
2
Views
285
dhookom
dhookom
TheresAlwaysAWay
  • Locked
  • Question
Can not get Access 2016 queries to accept form value input or TempVars! input 2
2
Replies
21
Views
427
TheresAlwaysAWay
TheresAlwaysAWay
TrekBiker
  • Locked
  • Question
Select addresses using two criteria, one 'Like" 2
Replies
3
Views
147
TrekBiker
TrekBiker
Moss100
  • Locked
  • Question
The following is not working - can
Replies
2
Views
102
Moss100
Moss100

Part and Inventory Search

Sponsor

Back
Top