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.

Excel: Extract Only Numbers From Text String

Extract Only Numbers From Text String

  1. You will need to insert this formula as an array.
  2. 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))) }