Oracle SUBSTR Function


/ Published in: SQL
Save to your folder(s)

A few examples of the Oracle SUBSTR function.


Copy this code and paste it in your HTML
  1. /* SUBSTR Examples */
  2.  
  3.  
  4. /* 1 - Example with Both Parameters */
  5.  
  6. SELECT 'Complete IT Professional',
  7. SUBSTR('Complete IT Professional', 1, 10) AS SUB
  8. FROM DUAL;
  9.  
  10. /* 2 - Example with Only Start Position */
  11.  
  12. SELECT 'Complete IT Professional',
  13. SUBSTR('Complete IT Professional', 5) AS SUB
  14. FROM DUAL;
  15.  
  16.  
  17. /* 3 - Example in Reverse */
  18.  
  19. SELECT 'Complete IT Professional',
  20. SUBSTR('Complete IT Professional', -1, 5) AS SUB
  21. FROM DUAL;
  22.  
  23.  
  24. /* 4 - Example in Reverse */
  25.  
  26. SELECT 'Complete IT Professional',
  27. SUBSTR('Complete IT Professional', -5, 5) AS SUB
  28. FROM DUAL;
  29.  
  30. /* 5 - Example of SUBSTR with INSTR Combined */
  31.  
  32. SELECT 'Complete IT Professional',
  33. SUBSTR('Complete IT Professional',
  34. INSTR('Complete IT Professional', ' ', 1, 1)) AS SUB
  35. FROM DUAL;
  36.  
  37.  
  38. /* 6 - Example of SUBSTR with INSTR Combined */
  39.  
  40. SELECT 'Complete IT Professional',
  41. SUBSTR('Complete IT Professional', 1,
  42. INSTR('Complete IT Professional', ' ', 1, 1)) AS SUB
  43. FROM DUAL;
  44.  
  45.  
  46. /* 7 - Example of SUBSTR with INSTR Combined */
  47.  
  48. SELECT 'Complete IT Professional',
  49. SUBSTR('Complete IT Professional',
  50. INSTR('Complete IT Professional', ' ', 1, 1)+1,
  51. INSTR('Complete IT Professional', ' ', 1, 2)-INSTR('Complete IT Professional', ' ', 1, 1)-1) AS SUB
  52. FROM DUAL;
  53.  
  54. /* 8 - Example to remove last character */
  55.  
  56. SELECT 'Complete IT Professional',
  57. SUBSTR('Complete IT Professional', 0,
  58. LENGTH('Complete IT Professional') - 1) AS SUB
  59. FROM DUAL;
  60.  
  61.  
  62. /* 9 - Example to remove last 5 characters */
  63.  
  64. SELECT 'Complete IT Professional',
  65. SUBSTR('Complete IT Professional', 0,
  66. LENGTH('Complete IT Professional') - 5) AS SUB
  67. FROM DUAL;

URL: http://www.databasestar.com/oracle-substr/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.