Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Giorgio Arata

By: a guest on Jan 9th, 2008  |  syntax: Oracle 8  |  size: 0.93 KB  |  views: 1,311  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
This paste has a previous version, view the difference. Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. CREATE OR REPLACE FUNCTION is_numeric(number_str VARCHAR2) RETURN BOOLEAN IS
  2.  x_is_numeric PLS_INTEGER := 0;
  3. BEGIN
  4.  SELECT XMLELEMENT ("ROWSET",
  5.  XMLAGG (
  6.  XMLELEMENT("ROW", xmlattributes(ROWNUM AS "num"),
  7.  XMLELEMENT ("STRING_VAL", REPLACE(number_str, ',', '.'))
  8.  ))
  9.  ).transform(XMLTYPE.createXML(
  10.      '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">' ||
  11.      '<xsl:output method="text"/>' ||
  12.      '<xsl:template match="ROW">' ||
  13.      '<xsl:variable name="str"><xsl:value-of select="STRING_VAL"/></xsl:variable>' ||
  14.      '<xsl:variable name="num">' ||
  15.      ' <xsl:if test="number($str)=number($str)">' ||
  16.      '  <xsl:value-of select="''X''"/>' ||
  17.      ' </xsl:if>' ||
  18.      '</xsl:variable>' ||
  19.      '<xsl:value-of select="string-length($num)"/>' ||
  20.      '</xsl:template>' ||
  21.      '</xsl:stylesheet>')).getStringVal() AS RES
  22.  INTO x_is_numeric
  23.  FROM dual;
  24.  RETURN x_is_numeric > 0;
  25. END is_numeric;