What is the difference between If I use SUBSTR (last_name,-1,1)='n'; or last_name is like'_n' or '%n' for the all the names in last_name which end by letter 'n'
Question asked by visitor Rajesh
What is the difference between If I use SUBSTR (last_name,-1,1)='n'; or last_name is like'_n' or '%n' for the all the names in last_name which end by letter 'n'
Question asked by visitor Rajesh
Like is used for pattern matching .
_n means any Last_name where the second character is n.
%n means any Last_name where the last character is n.
SUBSTR (last_name,-1,1)='n'
it will return the last leter of the string
In Oracle _ and % are Wild characters, so _ means exactly 1 ..in case of '_n' means max length is 2 and last character has to be n.
But % is for 0 or any no. of characters/numbers, so %n means length could be any but last character has to be n.
Both the wild card characters are used only for patternmatching for charater data not for numbers.
substr(char,m [,n])
purpose: returns a portion of char, beginning at character m, n characters
long. If m is positive, oracle counts from the beginning of char to
find the first character. If m is negative, oracle counts backwards
from the end of char. The value m cannot be 0. If n is omitted,
oracle returns all characters to the end of char. The value n
cannot be less than 1.
examples:
select substr('abcdefg',3,2) "substring" from dual
substring
cd
select substr('abcdefg',-3,2) "reversed substring" from dual
reversed substring
ef
LIKE
The like condition allows you to use wildcards in the where clause of an sql statement. This allows you to perform pattern matching. The like condition can be used in any valid sql statement - select, insert, update, or delete.
the patterns that you can choose from are:
% allows you to match any string of any length (including zero length)
_ allows you to match on a single character
select * from emp where ename like '_n' return records form emp where second char in ename starts with 'n'
select * from emp where ename like '%n' return records from emp where ename ends with 'n'
Last edited by susarlasireesha; 11-10-2007 at 04:49 AM.