Pattern Matching in Search Conditions
The LIKE keyword searches for character string, date, or time values that
match a specified pattern. For more information, see Data Types. The LIKE
keyword uses a regular expression to contain the pattern that the values are
matched against. The pattern contains the character string to search for,
which can contain any combination of four wildcards.
Wildcard Meaning
% Any string of zero or more characters.
_ Any single character.
[ ] Any single character within the specified range (for example,
[a-f]) or set (for example, [abcdef]).
[^] Any single character not within the specified range (for example,
[^a - f]) or set (for example, [^abcdef]).
Enclose the wildcard(s) and the character string in single quotation marks,
for example:
a.. LIKE 'Mc%' searches for all strings that begin with the letters Mc
(McBadden).
b.. LIKE '%inger' searches for all strings that end with the letters inger
(Ringer, Stringer).
c.. LIKE '%en%' searches for all strings that contain the letters en
anywhere in the string (Bennet, Green, McBadden).
d.. LIKE '_heryl' searches for all six-letter names ending with the
letters heryl (Cheryl, Sheryl).
e.. LIKE '[CK]ars[eo]n' searches for Carsen, Karsen, Carson, and Karson
(Carson).
f.. LIKE '[M-Z]inger' searches for all names ending with the letters inger
that begin with any single letter from M through Z (Ringer).
g.. LIKE 'M[^c]%' searches for all names beginning with the letter M that
do not have the letter c as the second letter (MacFeather).
This query finds all phone numbers in the authors table that have area code
415:
SELECT phone
FROM pubs.dbo.authors
WHERE phone LIKE '415%'
You can use NOT LIKE with the same wildcards. To find all phone numbers in
the authors table that have area codes other than 415, use either of these
equivalent queries:
SELECT phone
FROM pubs.dbo.authors
WHERE phone NOT LIKE '415%'
-- Or
SELECT phone
FROM pubs.dbo.authors
WHERE NOT phone LIKE '415%'
The IS NOT NULL clause can be used with wildcards and the LIKE clause. For
example, this query retrieves telephone numbers from the authors table in
which the telephone number begins with 415 and IS NOT NULL:
USE pubs
SELECT phone
FROM authors
WHERE phone LIKE '415%' and phone IS NOT NULL
Important The output for statements involving the LIKE keyword depends on
the sort order chosen during installation. For more information about the
effects of different sort orders, see Collations.
The only WHERE conditions that you can use on text columns are LIKE, IS
NULL, or PATINDEX.
Wildcards used without LIKE are interpreted as constants rather than as a
pattern, that is, they represent only their own values. The following query
attempts to find any phone numbers that consist of the four characters 415%
only. It will not find phone numbers that start with 415. For more
information about constants, see Using Constants.
SELECT phone
FROM pubs.dbo.authors
WHERE phone = '415%'
Another important consideration in using wildcards is their effect on
performance. If a wildcard begins the expression, an index cannot be used.
(Just as you wouldn't know where to start in a phone book if given the name
'%mith', not 'Smith'.) A wildcard in or at the end of an expression does not
preclude use of an index (just as in a phone book, you would know where to
search if the name was 'Samuel%', regardless of whether the names Samuels
and Samuelson are both there).
Searching for Wildcard Characters
You can search for wildcard characters. There are two methods for specifying
a character that would ordinarily be a wildcard:
a.. Use the ESCAPE keyword to define an escape character. When the escape
character is placed in front of the wildcard in the pattern, the wildcard is
interpreted as a character. For example, to search for the string 5%
anywhere in a string, use:
WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
In this LIKE clause, the leading and ending percent signs (%) are
interpreted as wildcards, and the percent sign preceded by a slash (/) is
interpreted as the % character.
b.. Use square brackets ([ ]) to enclose the wildcard by itself. To search
for a dash (-), rather than using it to specify a search range, use the dash
as the first character inside a set of brackets:
WHERE ColumnA LIKE '9[-]5'
The table shows the use of wildcards enclosed in square brackets.
Symbol Meaning
LIKE '5[%]' 5%
LIKE '5%' 5 followed by any string of 0 or more characters
LIKE '[_]n' _n
LIKE '_n' an, in, on (and so on)
LIKE '[a-cdf]' a, b, c, d, or f
LIKE '[-acdf]' -, a, c, d, or f
LIKE '[ [ ]' [
LIKE ']' ]
When string comparisons are performed with LIKE, all characters in the
pattern string are significant, including every leading and/or trailing
blank (space). If a comparison to return all rows with a string LIKE 'abc '
(abc followed by a single space) is requested, a row in which the value of
that column is abc (abc without a space) is not returned. The reverse,
however, is not true. Trailing blanks in the expression to which the pattern
is matched are ignored. If a comparison to return all rows with a string
LIKE 'abc' (abc without a space) is requested, all rows that start with abc
and have zero or more trailing blanks are returned.
----- Original Message -----
From: "Abhith P" <[EMAIL PROTECTED]>
To: "SQL" <[email protected]>
Sent: Saturday, September 02, 2006 10:28 AM
Subject: can i use decode? If so how
In my table OM_ITEM, it contains item code in VARCHAR format... Usually the
ITEM_CODE like 'ABCD_0001001, ABCD_0001002, ABCD_0001003' this.
I want to check whether the second portion of the ITEM_CODE contains any
charactor instead of number... And i need the result in the following format
ITEM CODE RESULT
ABC_0001001 NUMERIC
ABC_0001002 NUMERIC
ABC_000L002 NOT NUMERIC
ABC_000i005 NOT NUMERIC
So which query i have to execute to get the desired output
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2556
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
