Menu
I am trying to fetch an id from an oracle table. It's something like TN0001234567890345. What I want is to sort the values according to the right most 10 positions (e.g. 4567890345). I am using Oracle 11g. Is there any function to cut the rightmost 10 places in Oracle SQL ?
Thanks in advance
tismon
753
Oracle SQL doesn't have LEFT and RIGHT functions. They can be emulated with SUBSTR and LENGTH.
CHR(10) -- Line feed. CHR(13) -- Carriage return. You can use them in insert like this, INSERT INTO table_name (columne_name) VALUES ('ABC' || CHR (9) || 'DEF' || CHR (10) || 'GHI' || CHR (13) || 'JKL') Here is the complete list of ascii values.
Because the ASCII decimal code of 39 produces a single quote when passed to CHR, the expression CHR(39) can be used instead as shown next: select CHR(39) || last_name || CHR(39) from employees; For me, this is more readable. Similarly, even more difficult characters can be represented with the CHR function.
The INSTR functions search string for substring . The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters.
You can use SUBSTR function as:
select substr('TN0001234567890345',-10) from dual; Output:
4567890345 codaddict's solution works if your string is known to be at least as long as the length it is to be trimmed to. However, if you could have shorter strings (e.g. trimming to last 10 characters and one of the strings to trim is 'abc') this returns null which is likely not what you want.
Thus, here's the slightly modified version that will take rightmost 10 characters regardless of length as long as they are present:
select substr(colName, -least(length(colName), 10)) from tableName; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
