Regex: query examples and website links

July 28, 2017October 19, 2017 Discovery2012 String Functions

source link:

OS_PostR_RECT_620d0f5d10-b28e_COMPLETE_NP_FFP.CSV

OS_Rec_A_RECT_620d0f5d10-b28e_COMPLETE_NP_FFP.CSV

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

2017_M04_Postrec_July_2017_Basildon & Brentwood_99E_allprov_20171003_AiC.zip
2017_M05_rec_August_2017_Basildon & Brentwood_99E_ALLprov_20171003_AiC.zip
2017_M05_rec_August_2017_Basildon & Brentwood_99E_nt2048_20171003_AiC.zip
2017_M05_rec_August_2017_Basildon & Brentwood_99E_rddRF4_20171003_AiC.zip

Regular expression to find month names:

(\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:

T2O PostRec EMMain:

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]+)

Regular expression to select archive name:

(?<FinancialYear>[0-9]{4})_(?<FinancialMonthName>[a-z]+[0-9]{2})_(?<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)?))_(?<CalendarYear>[0-9]{4})_(?<CCGName>(?:[a-z& _]+))_(?<CCGCode>[0-9][0-9a-z]{2})_(?<ExtractType>[a-z0-9]+)_(?<Date>[0-9]{8})_(?<ProcessorType>[a-z]{3})(?<FileExtension>[.]+zip)

Regular expression to select RECT files:

(OS)_((Post)([a-zA-Z])|(Rec)(_[A-Z]))_RECT_([a-zA-z0-9_])*FFP.csv

(?<ExtractSetType>OS)_((?<ConcilationPostRec>Post)([a-z])*|(?<ConcilationRec>Rec)(_[a-z])*)_(?<FileType>RECT)_([a-z0-9-_])*(?<ProcessorTypeRECT>COMPLETE_NP_FFP).(?<FileExtension>csv)

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

November 4, 2016March 6, 2017 Discovery2012 SQL Query, String Functions

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

September 29, 2016November 4, 2016 Discovery2012 NULL Functions, SQL Query, String Functions

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: How to pad with zeros to a integer in SSIS

May 6, 2016November 4, 2016 Discovery2012 SSIS, String Functions

My integer number can have value 1, 2, ….11,….124,..

My desired output is 0001.opt, 0002.opt,…..0011.opt

RIGHT(REPLICATE(“0”, 8) + (DT_WSTR, 8)[IntegerColumn], 8)

Replace “8” with the desired width. The above expression generates a string value, not a numeric.

SSIS String: Replace empty string with null for integer column

September 1, 2015November 4, 2016 Discovery2012 NULL Functions, SSIS, String Functions

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

August 27, 2015November 4, 2016 Discovery2012 Date functions, SQL Server, SSIS, String Functions

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

August 25, 2015November 4, 2016 Discovery2012 SSIS, String Functions

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.

Excel: Extract Only Numbers From Text String

March 17, 2015November 4, 2016 Discovery2012 MS Office, String Functions

Extract Only Numbers From Text String

You will need to insert this formula as an array.
Select a cell in Excel, paste the formula and then press CTRL + SHIFT + ENTER. OR Excel formula surrounds the formula with braces { }.Formula: =MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$101),1)),0),COUNT(1*MID(A1,ROW($1:$101),1)))Formula with braces : {=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$101),1)),0),COUNT(1*MID(A1,ROW($1:$101),1))) }

ASP.NET: How to encrypt query string

February 12, 2015November 4, 2016 Discovery2012 ASP.NET, String Functions

Is your email address OK? You are signed up for our newsletters but your email address is either unconfirmed, or has not been reconfirmed in a long time. Please click here to have a confirmation email sent so we can confirm your email address and start sending you newsletters again. Alternatively, you can update your subscriptions.

Download source code – 4.7 KB

Introduction

Using query strings to send data from the browser to the server is a widespread approach. Giving the visitor of a web application the opportunity of modifying query strings by transmitting them in clear text, is certainly a potential security threat.
Thus, I encourage developers to encrypt query strings, even if they do not contain confidential data. However, I am aware that it is still possible to alternate an encrypted query string, but with an appropriate exception handling, this is harmless.

Background

To keep this article simple, I used a contradictable encryption (DES encoding), though any cutting-edgeencryption can be easily applied to the samples given.

Using the code

So, let’s get down to business. The main part of the presented solution consists of a HttpModule which decrypts the query string and hence provides the page request with the ordinary unencrypted query strings:

Collapse | Copy Code

using System;
using System.Web;
using System.Web.Configuration;

namespace HelveticSolutions.QueryStringEncryption
{
    /// <summary>
    /// Http module that handles encrypted query strings.
    /// </summary>
    public class CryptoQueryStringUrlRemapper : IHttpModule
    {
        #region IHttpModule Members

        /// <summary>
        /// Initialize the http module.
        /// </summary>
        /// <param name="application">Application,
        ///           that called this module.</param>
        public void Init(HttpApplication application)
        {
            // Attach the acquire request state event
            // to catch the encrypted query string
            application.AcquireRequestState += application_AcquireRequestState;
        }

        public void Dispose()
        {}
    
        #endregion

        /// <summary>
        /// Event, that is called when the application acquires the request state.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public void application_AcquireRequestState(object sender, EventArgs e)
        {
            // Get http context from the caller.
            HttpApplication application = (HttpApplication) sender;
            HttpContext context = application.Context;

            // Check for encrypted query string
            string encryptedQueryString = context.Request.QueryString["request"];
            if (!string.IsNullOrEmpty(encryptedQueryString))
            {
                // Decrypt query strings
                string cryptoKey = WebConfigurationManager.AppSettings["CryptoKey"];
                string decryptedQueryString = 
                  CryptoQueryStringHandler.DecryptQueryStrings(encryptedQueryString, 
                                                               cryptoKey);
                context.Server.Transfer(
                  context.Request.AppRelativeCurrentExecutionFilePath + 
                  "?" + decryptedQueryString);
            }
        }
    }
}

As you might have noticed, if there is an encrypted query string for the current request, the module automatically terminates the execution of the current page and internally starts execution of a new request on the server.
The next step is to register the HttpModule in the web.config file:

Collapse | Copy Code

<httpModules>
    <add name="CryptoQueryStringUrlRemapper" 
      type="HelveticSolutions.QueryStringEncryption.CryptoQueryStringUrlRemapper"/>
</httpModules>

Last but not least, do not forget to encrypt query strings before sending them back to the server:

Collapse | Copy Code

private void PrepareSendButton()
{
    NameValueCollection queryStrings = new NameValueCollection();
    queryStrings.Add("param1", "Test1");
    queryStrings.Add("param2", "Test2");
    queryStrings.Add("param3", "Test3");

    // Encrypt query strings
    string encryptedString = CryptoQueryStringHandler.EncryptQueryStrings(
      queryStrings, WebConfigurationManager.AppSettings["CryptoKey"]);
    btnSendParams.PostBackUrl = string.Concat("~/Default.aspx?", encryptedString);
}

As outlined earlier in this article, the encryption class can be easily replaced by any other encryption class. A full running sample can be downloaded above.

Important issue

The method DecryptQueryStrings in the CryptoQueryStringHandler contains the following line :

Collapse | Copy Code

return Encryption64.Decrypt(encryptedStrings.Replace(" ", "+"), key);

For unknown reasons, the request replaces every ‘+’ character in the query with an empty character.

SQL Query: How to include single inverted comma in a query

January 28, 2015November 4, 2016 Discovery2012 SQL Server, String Functions

1st Method:

Declare @Customer varchar(255),
@sqlstring varchar(4000)
Set @Customer = ‘Customer_Name’

set @sqlstring = ‘Select Customerid from Customer Where name = ‘” + @Customer + ””
print @sqlstring

Output is:

Select Customerid from Customer Where name = ‘Customer_Name’

Note:
Actually to include a ‘ inverted comma in the search you just add one more extra ” inverted comma in front of it.

So where name =’Joshua’ is will search for string Joshua, to search for string ‘Joshua’ including single inverted comma, we first add the single inverted comma to include in the search and then one more as a escape character, so it will be where name=”’Joshua”’ which will search for string ‘Joshua’.

If you look closely @sqlstring = ‘Select Customerid from Customer Where name = ‘” + @Customer + ”” you will see after name there are three single inverted comma and after @Customer there are four single inverted comma. So, how come? Lets break down this. Our target string would be –

Select Customerid from Customer Where name = ‘Customer_Name’
So according to rule lets add one more extra comma single inverted comma
Select Customerid from Customer Where name = ”Customer_Name”

now to take the Customer_Name as parameter
set @sqlstring = ‘Select Customerid from Customer Where name = ‘‘ ‘ + @Customer + ‘ ‘‘ ‘

one single inverted comma for the beginning of the string and one single inverted comma for the end the string. so to include two inverted comma, it would ””

2nd method:

select quotename(Customer_Name,””)

Masud Ahmed

SQL server 2012 database developer and Website developer

String Functions

Regex: query examples and website links

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

SQL String: Replace NULL string literal with null value

SSIS: How to pad with zeros to a integer in SSIS

SSIS String: Replace empty string with null for integer column

SQL: Getting the date with leading zeros

SSIS String: Remove spaces from all string columns

Do ‘something’ for all columns in your dataflow

Excel: Extract Only Numbers From Text String

Extract Only Numbers From Text String

ASP.NET: How to encrypt query string

Query string encryption for ASP.NET

Introduction

Background

Using the code

Important issue

SQL Query: How to include single inverted comma in a query