Using Regular Expression Within a Stored Procedure

Using regular expression within a stored procedure

If you want true regular expression pattern matching you will need to roll your own CLR UDF. This link goes over how to do that:

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Keep in mind that you can only do this in SQL Server 2005 or higher.

If you just want non-alpha you can do this:

'([^a-z])'

Here is the documentation for SQL Server like:

http://msdn.microsoft.com/en-us/library/ms179859.aspx

REGEX in stored procedure in =/like clause

Unfortunately there is no regexp replace function in SQL Server. The closest you can get is by using PATINDEX to locate the <{ and }>' characters and STUFF` everything in between:

SELECT t.*, CASE WHEN val1_new = val2_new THEN 'match' END
FROM (VALUES
    ('SOMETHING\<1>\SOMETHING',  'SOMETHING\{2}\SOMETHING'),
    ('SOMETHING\BLABLA\<1>\BLA', 'SOMETHING\BLABLA\{2}\BLA'),
    ('SOME\<1>\THING\BLA',       'SOMETHING\{2}\BLABLA'),
    ('dog',                      'dog')
) AS t(val1, val2)
CROSS APPLY (
    SELECT
        PATINDEX('%[<{]%', val1) AS val1_pos1,
        PATINDEX('%[>}]%', val1) AS val1_pos2,
        PATINDEX('%[<{]%', val2) AS val2_pos1,
        PATINDEX('%[>}]%', val2) AS val2_pos2
) AS ca1
CROSS APPLY (
    SELECT
        CASE WHEN val1_pos1 > 0 AND val1_pos2 > 0 THEN STUFF(val1, val1_pos1, val1_pos2 - val1_pos1 + 1, '') ELSE val1 END,
        CASE WHEN val2_pos1 > 0 AND val2_pos2 > 0 THEN STUFF(val2, val2_pos1, val2_pos2 - val2_pos1 + 1, '') ELSE val2 END
) AS ca3(val1_new, val2_new)

Regular expressions in stored procedures

Generally, yes.

MySQL: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Oracle: http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm

MS SQL: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Some more:

PostgreSQL: http://www.postgresql.org/docs/8.3/static/functions-matching.html

DB2: http://www.ibm.com/developerworks/data/library/techarticle/0301stolze/0301stolze.html

Informix: http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/db_regexp.html

SQL Anywhere: http://iablog.sybase.com/paulley/2009/06/using-regular-expressions-with-sql-anywhere/

Read just like regex but in SQL Server stored procedure

You can use the patindex and substring function for this.

Code below works for the given example.

Declare @txt varchar(max) = '/FILES/Project/1611\1/thumbs/8dff051e-ff77-4540-ae4b-f643960bbdad_logo.png.80x80.jpg'

select SUBSTRING(txt,startPosition,EndPosition-StartPosition)
from
(
    select PATINDEX('%/thumbs/%',@txt) endPosition,patindex('%\%',@txt)+1 StartPosition, @txt txt
)p

Regex to detect WITH option in a stored procedure

Even though it isn't a real answer, I found the easier way to do this was to enforce some naming convention rules in our stored procedures and modify the 20 or so that violated the rules.

Indeed, this is a case when Regex is not the solution!

Reg_exp in stored procedure needs into

you may use cursors for multiple-row returns :

SQL> set serveroutput on;
SQL> declare
      v_abc  varchar2(1500); 
  begin
  for c in ( select regexp_substr('hello,world', '[^(,|;|\s|&)]+', 1, level) abc
               from dual
             connect by regexp_substr('hello,world', '[^(,|;|\s|&)]+', 1, level) is not null )
  loop
   v_abc := c.abc;
   dbms_output.put_line(v_abc);
  end loop;
end;

Extract string using regex in stored procedure

I was using the wrong method to extract string out of expression. The correct version is as follows if anyone is interested:

select (regexp_matches('BLM2016', '([0-9]?[A-Z]+?)\s*(?:FM)?[FGHJKMNQUVXZ](?:[02]0)?[12]?[0-9]', 'gi'))[1]

How to perform sql procedure by java using regexp as parameter in procedure?

The problem is with the single quotation mark (apostrophe), as it is also used in the SQL command. You either need to escape it (\'), or - if it is used in some XML - use the ' entity.

If you do not want to allow apostrophes in the matches, just remove it from the regex.

---

Related Topics