Regex: query examples and website links

source link:

  1. debuggex.com
  2. regex101.com

 

OS_PostRec_June 2017_EMMain_07L_1708201_7163015_T2O_f29c00f7f2_FFP.ctrl
OS_PostRec_June 2017_EMMain_07L_1708201_7163015_T2O_f29c00f7f2_FFP.txt
OS_PostRec_June 2017_EMMain_07L_170820163023_0ab98771e1_T2O_20170904140654_20170904_141835_FFP.txt

OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt
OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP.txt

OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018
OS_Rec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018
OS_PostRec_April 2017_EMMain_EMError_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_EPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_OPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

OS_Rec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_PostRec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

OS_Rec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

OS_PostRec_April 2017_SPMain_06Q_26052017082756_a8abaa7c46_20170530_174428_T2O Feb 2018

 

Regular expression to find month names:

(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))

 

(\b\d{1,2}\D{0,3})?\b(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|(Nov|Dec)(?:ember)?)\D?(\d{1,2}\D?)?\D?((19[7-9]\d|20\d{2})|\d{2})

 

Regular expression tofind Month Year:

((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))

 

 

 

RecOrPostRec:

(?:[_])(?<RecOrPostRec>(?:(Post)?(?:Rec)))(?:[_])

 

CCGCode:

(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])

 

FileType (EMMain/EPMain):

(?:[_])(?<FileType>[a-zA-Z]{6,15})(?:[_])(?:(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_]))

Save:

(?<RecOrPostRec>_Rec_)|(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))|(?<Year>[0-9]{4}_)|(?<FileType>_EMMain_)|(?<CCGCode>_[0-9][0-9a-zA-Z]{2}_)

Full regex – never change or delete:

(?:[_])(?<RecOrPostRec>(?:(Post)?(?:Rec)))(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>[a-zA-Z]{6,15})(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>(?:FFP)|(?:T2O))

Full regex – v2:

(?<ExtractSetType>[0-9a-z]+)_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[ ](?<Year>[0-9]{4})_(?<FileType>(?:[a-z]{2,15}_)?[a-z]{2,15})_(?<CCGCode>[0-9][0-9a-z]{2})_(?:(?<UniquePart>[a-zA-Z0-9_]*))_(?<ProcessorType>FFP|T2O)

AiC Rec EMMain:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

AiC PostRec EMMain:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

T2O Rec EMMain:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

T2O PostRec EMMain:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>EMMain)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

 

 

 

AiC Rec EMError:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

AiC PostRec EMError:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>FFP)

T2O Rec EMError:

(?:[_])(?<Rec>Rec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

T2O PostRec EMError:

(?:[_])(?<PostRec>PostRec)(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])?(?:[a-zA-Z]{6,15})?(?:[_])(?<FileType>EMError)(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])(?:(?<UniquePart>[a-zA-Z0-9_]*))(?:[_])(?<Processor>T2O)

 

 

Current version:

 

(?<ExtractSetType>[0-9a-z]+)_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[ ](?<Year>[0-9]{4})_(?<FileType>(?:[a-z0-9]{2,15}_)?[a-z0-9]{2,15})_(?<CCGCode>[0-9][0-9a-z]{2})_(?:(?<UniquePart>[a-zA-Z0-9_]*))_(?<ProcessorType>FFP|T2O)(?<Extra>\S*)(?<FileExtension>[.]+[a-z]+)

 

(?<ExtractSetType>[0-9a-z]+)_(?<ConcilationType>(?:Post)?Rec)_(?:(?<Month>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[ ](?<Year>[0-9]{4})_(?<FileType>(?:[a-z0-9]{2,15}_)?[a-z0-9]{2,15})_(?<CCGCode>[0-9][0-9a-z]{2})_(?:(?<UniquePart1>[a-zA-Z0-9_]*))_(?<T2O>T2O)?_(?:(?<UniquePart2>[a-zA-Z0-9_]*))_(?<FFP>FFP)(?<FileExtension>[.]+[a-z]+)

 

 

Advertisements

String functions: Remove non-alpha characters, remove multiple spaces

Search hint:

remove non-alpha characters from a string

replace multiple spaces with single space

remove multiple spaces

convert to title case

convert to name case

=============================

DECLARE @inputString nvarchar(1000);
/*remove non-alpha characters from a string*/
SET @inputString=’ (My name is): MR. [Masud AHMED]. \Some unknown /characters- ÄÄ ÖÖ ÜÜ ÉÉ ØØ ?? ÆÆ ‘;
PRINT ‘input string: ‘ + @inputString;

DECLARE @NonAlphaList AS VARCHAR(50);
/*list of all the non-alpha characters except a space ( ) and a dot (.) I want to preserve spaces between words*/
SET @NonAlphaList = ‘%[^a-z .]%’;
/*look for the non-alpha character position in a string*/
WHILE PATINDEX(@NonAlphaList, @inputString) > 0
/*delete the non-alpha character and replace the character with nothing*/
SET @inputString = STUFF(@inputString, PATINDEX(@NonAlphaList, @inputString), 1, ”);
PRINT ‘removed non-alpha: ‘ + @inputString;

/*remove multiple spaces*/
/*select string = replace(replace(replace(@inputString,’ ‘,'<>’),’><‘,”),'<>’,’ ‘)*/
SET @inputString= REPLACE(@inputString, ‘ ‘, ‘<>’);
SET @inputString= REPLACE(@inputString, ‘><‘, ”);
SET @inputString= REPLACE(@inputString, ‘<>’, ‘ ‘);
SET @inputString=LTRIM(RTRIM(@inputString));
PRINT ‘removed multiple spaces: ‘ + @inputString;

/*Title Case*/
DECLARE @index tinyint=1;
DECLARE @currentChar CHAR(1);
SET @inputString=LOWER(@inputString);
WHILE @index<LEN(@inputString)
BEGIN
SET @currentChar=SUBSTRING(@inputString, @index, 1);
IF (@index=1)
SET @inputString=STUFF(@inputString, @index, 1, UPPER(@currentChar))
IF(@currentChar=’ ‘)
SET @inputString=STUFF(@inputString, (@index + 1), 1, UPPER(SUBSTRING(@inputString, @index + 1, 1)))
SET @index+=1;
END

PRINT ‘convert to Title Case: ‘ + @inputString;

SQL String: Replace NULL string literal with null value

Scenario:

I have a table with a string literal value ‘NULL’ or ‘null’ and I want to repalce them with the null value.

Solution:

NULLIF Returns a null value if the two specified expressions are equal.

NULLIF ( expression , expression )

You can use NULLIF:

/*here it will compare mark column value with string 'null' if they are equal it will return null*/
SELECT NULLIF(Mark,'null') 
FROM tblname;

SSIS String: Replace empty string with null for integer column

Scenario:

My source SQL table column was ‘nvarchar(50)’. My destination SQL table column was ‘money‘ data type. Some of my source column had a blank space value. So I had to convert them first to NULL(DT_WSTR, 10) if it is empty string then convert them to currency.

Expression: (DT_CY) (TRIM(Gift_amt)==”” ?  NULL(DT_WSTR,10)  :   [Gift_amt])

 

Note:

(DT_I4)( TRIM( «character_expression»  ) == “” ? NULL(DT_WSTR, 50)  :  «character_expression»  )

(DT_I4)( TRIM( Campaign) == “” ? NULL(DT_WSTR, 50) :  Campaign)

SQL: Getting the date with leading zeros

I was extracting MONTH(GETDATE()) and it was returning month 8 rather than 08.

I can get the desired result using the SQL RIGHT string function and pad it with 00 and take right 2 numbers.

DECLARE @day CHAR(2)

SET @day = RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DAY, GETDATE())), 2)

print @day

SSIS expression:  RIGHT("00" + (DT_WSTR, 2) MONTH( GETDATE()), 2)
SSIS YYYYMMDD - return string date: (DT_WSTR,4) YEAR( GETDATE()  ) + RIGHT("00" + (DT_WSTR, 2) MONTH( GETDATE()), 2) +  RIGHT("00" + (DT_WSTR, 2) DAY( GETDATE()), 2)
SSIS YYYMMDD - return integer date: (DT_I4) ((DT_WSTR,4) YEAR( GETDATE()  ) + RIGHT("00" + (DT_WSTR, 2) MONTH( GETDATE()), 2) +  RIGHT("00" + (DT_WSTR, 2) DAY( GETDATE()), 2))

SSIS String: Remove spaces from all string columns

Do ‘something’ for all columns in your dataflow

 Case
If you have a lot columns that all need to be adjusted with the same function (let’s say uppercase). You could use the Derived Column component or the Character Map component to adjust all columns one by one. The Script Component can make your life easier.Starting point

Col1 Col2 Col3
test Test TEST
bla Bla BLA
xxx Xxx XXX

Desired situation

Col1 Col2 Col3
TEST TEST TEST
BLA BLA BLA
XXX XXX XXX

Solution
1) Add a script component to you dataflow.

Script component (transformation)

2) Select all columns (you need) and select ReadWrite.

ReadWrite Input columns
// This script adjusts the value of all string fields
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Reflection;            // Added
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    //  Method that will be started for each record in you dataflow
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Use Reflection to loop through all the properties of Row:
        // Example:
        // Row.Field1 (String)
        // Row.Field1_IsNull (Boolean)
        // Row.Field2 (String)
        // Row.Field2_IsNull (Boolean)
        foreach (PropertyInfo p in Row.GetType().GetProperties())
        {
            // Do something for all string properties: Row.Field1, Row.Field2, etc.
            if (object.ReferenceEquals(p.PropertyType, typeof(string)))
            {
                // Use a method to set the value of each String type property
                // Make sure the length of the new value doesn't exceed the column size
                p.SetValue(Row, DoSomething(p.GetValue(Row, null).ToString()), null);
            }
        }
    }
 
    // New function that you can adjust to suit your needs
    public string DoSomething(string ValueOfProperty)
    {
        // Uppercase the value
        ValueOfProperty = ValueOfProperty.ToUpper();
                  // trim leading and trailing spaces
ValueOfProperty = ValueOfProperty.Trim();
        return ValueOfProperty;
    }
}

4) Now add a target and run the package to see the result:

Added two data viewers to see the result.