COMANDOS RIGHT E LEFT NO PL/SQL ???

RICART 01/10/2010 08:10:02
#354241 Resposta escolhida

Boa pergunta, mas essa é bem simples:



Vc passa o parâmetro negativo:
substr([ô]Olhe para o passado[ô],-4) - > retornará [ô]sado[ô]
substr([ô]Olhe para o passado[ô],-4,2) - > retornará [ô]sa[ô]

veja mais: http://psoug.org/reference/substr_instr.html

Simplified Examples
Examples in Oracle/PLSQL of using the substr() function to extract a substring from a string:
The general syntax for the substr() function is:

substr( source_string, start_position, [ length ] )

[Ô]source_string[Ô] is the original source_string that the substring will be taken from.

[Ô]start_position[Ô] is the position in the source_string where you want to start extracting characters. The first position in the string is always [ô]1[ô], NOT [ô]0[ô], as in many other languages.

[Ô]length[Ô] is an optional parameter that specifies how many characters to extract. If this parameter is not used, SUBSTR will return everything from the start_position to the end of the string.

Notes:
If the start_position is specified as [Ô]0[Ô], substr treats start_position as [Ô]1[Ô], that is, as the first position in the string.

If the start_position is a positive number, then substr starts from the beginning of the string.

If the start_position is a negative number, then substr starts from the end of the string and counts backwards.

If the length is a negative number, then substr will return a NULL value.

Examples:

substr([ô]Dinner starts in one hour.[ô], 7, 6) will return [ô]starts[ô]
substr([ô]Dinner starts in one hour.[ô], 7) will return [ô]starts in one hour.[ô]
substr([ô]Dinner starts in one hour.[ô], 1, 6) will return [ô]Dinner[ô]
substr([ô]Dinner starts in one hour.[ô], -4, 3) will return [ô]our[ô]
substr([ô]Dinner starts in one hour.[ô], -9, 3) will return [ô]one[ô]
substr([ô]Dinner starts in one hour.[ô], -9, 2) will return [ô]on[ô]

VILMARBR 01/10/2010 12:02:45
#354250

RICART, muito obrigado !

Descobri uma outra forma tb q um colega no trampo acabou de me passar, zóia só :

-- retorna 234567

SELECT SUBSTR([ô]1234567[ô], 2, 6) FROM DUAL

-- retorna 34567

SELECT SUBSTR([ô]1234567[ô], 3, 5) FROM DUAL

-- retorna 4567

SELECT SUBSTR([ô]1234567[ô], 4, 4) FROM DUAL

-- retorna 567

SELECT SUBSTR([ô]1234567[ô], 5, 3) FROM DUAL

-- retorna 67

SELECT SUBSTR([ô]1234567[ô], 6, 2) FROM DUAL