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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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