Wednesday, March 5, 2014

T-SQL code to parse Numbers from a String


Someone asked me recently about T-SQL function to parse numbers from a string. Since there is no built-in function as such, I explained him the logic to calculate same thing using T-SQL code. I will explain the logic here, in case you need to implement same thing (or somewhat similar thing) in your project.

Let’s take example to understand the problem.

Input
Output
Hari12 Sharma34
1234
My 1st code is D$34A and 2nd code is E#078!!!
1342078
Hari# Sharma
 
$1#0?0 !?@1
1001

Here is the code to get the desired output:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'
DECLARE @Position SMALLINT = 0
SET @Position = PATINDEX('%[^0-9]%', @Str) --Find first character
 
WHILE (@Position > 0)
BEGIN
    -- Replace alphabet with empty string.
    SET @Str = STUFF(@Str, @Position, 1, '')
   
    -- Find next alphabet
    SET @Position = PATINDEX('%[^0-9]%', @Str)
END
SELECT @Str Result
 

Result
-------------------------------------
1342078

 
We can also write function to encapsulate this code and use wherever required.


This above code can be optimized by using Tally table mentioned below:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'
 
DECLARE @Result VARCHAR(100)
SELECT  @Result = COALESCE(@Result, '') + Digit
FROM    (
        SELECT  SUBSTRING(@Str, t.ID, 1) AS Digit
        FROM    dbo.Tally t
        WHERE   t.ID <= LEN(@Str)
        AND     PATINDEX('%[0-9]%', SUBString(@Str, t.ID, 1)) = 1
        ) stg
 
SELECT @Result Result




Result
-------------------------------------
1342078

Tuesday, January 14, 2014

SSIS 2012 - Package Configurations

When I started working on SSIS 2012 using SQL Server Data Tools (SSDT), I noticed that SSIS package configuration option was missing. It was available in previous version of SSIS under Control Flow. We could see package configuration by right click on Control Flow surface area. Unfortunately, that option is missing now.

So I am writing this blog to provide help in case you are running into the same issue. I will explain SSIS 2012 Package Configuration in case you are wondering how to implement Package Configuration in SSIS 2012. 

PROBLEM: Package Configuration option is missing as shown in below screenshot:

 
Here is what MSDN describes: Create Package Configuration. Unfortunately, I could not find “Package Configuration” option there as well. However, I got a hint from the Notes on the same MSDN link which says:
 
You can also access the Package Configuration Organizer, by clicking the ellipsis button next to the Configuration property. The Configuration property appears in the properties window for the package.


Follow these steps to create a package configuration:

1.      In SQL Server Data Tools (SSDT), open the Integration Services project.
2.      In Solution Explorer, double-click the package to open it.
3.      To open Package Configuration Organizer, click on the ellipse button next to the Configurations property.



4.      In the Package Configuration Organizer dialog box, select Enable package configurations, and then click Add button.


5.      On the welcome page of the Package Configuration Wizard page, click Next button.
6.      On the Select Configuration Type page, specify the configuration type, and then set the properties that are relevant to the configuration type.
I am using “XML configuration file” in Configuration type. Now specify configuration file path in configuration. Click Next button.


7.      On the Select Properties to Export page, select the properties of package objects to include in the configuration. If the configuration type supports only one property, the title of this wizard page is Select Target Property.


NoteNote
Only the XML Configuration File and SQL Server configuration types support including multiple properties in a configuration.

8.      On the Completing the Wizard page, type the name of the configuration, and then click Finish.


9.      View the configuration in the Package Configuration Organizer dialog box and click Close button.





I hope this post is helpful!