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'
[b]Question asked by visitor Rajesh[/b]
Printable View
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'
[b]Question asked by visitor Rajesh[/b]
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.
[quote=geek_guest;20111]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' [b]question asked by visitor rajesh[/b][/quote] substr(char,m [,n]) <br> purpose: returns a portion of char, beginning at character m, n characters <br> long. If m is positive, oracle counts from the beginning of char to <br> find the first character. If m is negative, oracle counts backwards <br> from the end of char. The value m cannot be 0. If n is omitted, <br> oracle returns all characters to the end of char. The value n <br> cannot be less than 1. <br> examples: <br> select substr('abcdefg',3,2) "substring" from dual <br> substring <br> cd <br> select substr('abcdefg',-3,2) "reversed substring" from dual <br> reversed substring <br> ef <br> LIKE <BR> 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. <br> the patterns that you can choose from are: <br> % allows you to match any string of any length (including zero length) <br> _ allows you to match on a single character <br> select * from emp where ename like '_n' return records form emp where second char in ename starts with 'n' <br> select * from emp where ename like '%n' return records from emp where ename ends with 'n' <br>