Rick Caminiti dotCOM

Short and sweet. Accept a varchar input parameter, try to convert it to a number. If the conversion fails for any reason, return FALSE, else, return TRUE.

CREATE OR REPLACE FUNCTION is_numeric (

   p_data   VARCHAR2

)

   RETURN NUMBER

IS

   v_number   NUMBER;

   o_bool     BOOLEAN := TRUE;

BEGIN

   BEGIN

      v_number := p_data;

   EXCEPTION

      WHEN OTHERS THEN

         o_bool := FALSE;

   END;

 

   RETURN o_bool;

END isnumber;

In a like statement, “%” is a wildcard for everything after or before it and “_” is a wildcard for one character.  Observe:

LIKE ‘%Z’ would return ANYthing that ended with Z.

 

LIKE ‘%Z%’ would return ANYthing with a Z in it.

 

LIKE ‘Z__’ would return anything that started with Z and had at least 2 characters after it, like:

ZOO, ZXY, ZAP

but would not return:

ZA, ZO, Z, or XYZ

 

LIKE ‘Z%’ would return anything that STARTED with Z.

 

LIKE ‘_Z%’ would return anything that had one character, then a Z, then anything or nothing else like:

YZX, YZ, ZZZ

but would not return:

XYZ, ABCZ

 

However, if you want to look for the literal value of ‘%’ or ‘_’, you need to escape it.  For instance, if you want to look for anything that begins with ‘Z_’, then you would need to escape the ‘_’, otherwise, you would be looking for anything that started with ‘Z’ followed by one wildcard character ‘_’, followed by a wildcard of any number of characters ‘%’, returning anything that just started with ‘Z’ and had at lease one character after it.

select table_catalog, column_name, table_schema, table_name
from information_schema.columns
where column_name like 'Z\_%' escape '\'

This would only return data that started with 'Z_'.

Here’s a quick and dirty way to send email via MSSQL.  For some reason, we’re not allowed to send email using the Email Task in SSIS, so I chose this option.  Unfortunately, even if you have access to execute sp_SQLSMTPMail, you might not have access to run xp_cmdshell.

 

I ended up having to create my SSIS package with a SQL Task that runs this proc, then I had to run it with a SQL Agent Job with a proxy account that had access to xp_cmdshell.  Fun stuff.

 

 

 

 

declare @developerperson varchar(80)

declare @message_body varchar(1000)

 

set @message_body =

‘Dear ‘ + @developerperson + ‘Use \n for line breaks and escape your ‘’s’

 

 

EXECUTE dbo.sp_SQLSMTPMail

      @vcTo = ‘You@you.com’,

      @vcCC= ‘Them@them.com’,

      @vcSubject = ‘Super Cool SQL Email’,

      @vcBody = @message_body,

      @vcFrom = ‘Me@me.com’,

      @vcSMTPServer = ‘SMTPName’,

      @vcSenderName = ‘Some One’,

      @vcServerName = @@SERVERNAME –no need to specify because this is the default anyway

Or, lets say you want to email query results as an attachment:

EXECUTE dbo.sp_SQLSMTPMail

  @vcTo=‘you@you.com’

, @vcCC=‘them@them.com’

, @vcSubject=‘Here is your wonderful report that is terribly formatted as a txt file attachment’

, @vcquery= ’select col1, col2, col3, col4 from database.schema.table’

 

 

 

 

 

 

 

 

Here’s the actual Proc definition: Read more…

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 491520 bytes) in /home1/golfcomp/public_html/rickcaminiti/wp-content/plugins/gd-star-rating/code/t2/templates.php on line 155