DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to filter by pattern?

Execute the following T-SQL script in Microsoft SSMS Query Editor to demonstrate the architecture of user-defined function to filter pattern-based string from plain text.

-- Pattern filtering UDF - currency amount filtering

-- Filter out all figures beginning with $ - scalar-valued function

CREATE FUNCTION fnFilter$Amount (@Input varchar(max))

RETURNS varchar(max) AS

BEGIN

  DECLARE @Output varchar(max) = '', @i int = 1;

  WHILE (@i <= len (@input))

  BEGIN

    IF (SUBSTRING(@Input, @i,1) != '$')

      BEGIN SET @Output += SUBSTRING(@Input, @i,1); SET @i += 1;

            CONTINUE; END

  SET @i +=1;

  WHILE (@i <= len (@input))

  BEGIN

    IF (SUBSTRING(@Input, @i,1) BETWEEN '0' and '9' OR

        SUBSTRING(@Input, @i,1) in ('.',','))

        BEGIN SET @i += 1; CONTINUE;END

    ELSE BREAK; 

  END

  END

 RETURN (@Output);

END

GO

 

SELECT dbo.fnFilter$Amount

 ('JPMorgan jumped 2.7 percent to $45.74 and Goldman Sachs rose 2.7 percent to $159.96')

-- JPMorgan jumped 2.7 percent to  and Goldman Sachs rose 2.7 percent to

------------

Related article:

Adam Machanic : Pattern-based replacement UDF

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE