Using SSIS ... Get only numbers from a column
-
Below is the sample SQL.
From Source1 tb, I need only Integer values of Value column into ValueNEW of Destination1 tb as shown in the output pic.
--Source1
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Source1]') AND type in (N'U'))
DROP TABLE [dbo].[Source1]
GO
CREATE TABLE [dbo].[Source1](
[ID] [int] NOT NULL,
[Value] [varchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Source1] VALUES(1, '45.212')
INSERT INTO [dbo].[Source1] VALUES(2, '12/45')
INSERT INTO [dbo].[Source1] VALUES(3, '6')
INSERT INTO [dbo].[Source1] VALUES(4, '100.56')
INSERT INTO [dbo].[Source1] VALUES(5, 'ADFJJ 12/34 F=10.4')
INSERT INTO [dbo].[Source1] VALUES(6, '+. 0.5')
INSERT INTO [dbo].[Source1] VALUES(7, '300')
INSERT INTO [dbo].[Source1] VALUES(8, '90')
INSERT INTO [dbo].[Source1] VALUES(9, '80.5')
INSERT INTO [dbo].[Source1] VALUES(10, '13): @FD E 19.50 32')
--Destination
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Destination1]') AND type in (N'U'))
DROP TABLE [dbo].[Destination1]
GO
CREATE TABLE [dbo].[Destination1](
[ID] [int] NOT NULL,
[Value] [varchar](255) NULL,
[ValueNEW] [varchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Destination1] VALUES(1, '45.212', NULL)
INSERT INTO [dbo].[Destination1] VALUES(2, '12/45', NULL)
INSERT INTO [dbo].[Destination1] VALUES(3, '6', '6')
INSERT INTO [dbo].[Destination1] VALUES(4, '100.56', NULL)
INSERT INTO [dbo].[Destination1] VALUES(5, 'ADFJJ 12/34 F=10.4', NULL)
INSERT INTO [dbo].[Destination1] VALUES(6, '+. 0.5', NULL)
INSERT INTO [dbo].[Destination1] VALUES(7, '300', 300)
INSERT INTO [dbo].[Destination1] VALUES(8, '90', 90)
INSERT INTO [dbo].[Destination1] VALUES(9, '80.5', NULL)
INSERT INTO [dbo].[Destination1] VALUES(10, '13): @FD E 19.50 32', NULL)
SELECT * FROM [dbo].[Source1]
SELECT * FROM [dbo].[Destination1]
Output PIC:
-
Do you want numeric values or integer values (whole numbers)? Based on the images you shared you want only integers.
Do you want to do it in SQL or SSIS?
If in SQL what version of SQL Server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Set your source as a query instead of a table, then use this:
SELECT ID,
Value,
CASE WHEN Value NOT LIKE '%[^0-9]%' THEN Value END AS ValueNEW
FROM [dbo].[Source1]
For an explanation on how this works, read the following article: http://qa.sqlservercentral.com/articles/ISNUMERIC()/71512/
-
Please, I need only Integers and in SSIS, thank you.
-
etirem (11/13/2015)
Please, I need only Integers and in SSIS, thank you.Is this a question for a test? Why do you have restrictions? Why are you cheating?
-
Absolutely not. It is for more than 200 M records and I need that to work in SSIS but struggling to get it.
In SQL, I'm using a function which is killing performance.
-
etirem (11/13/2015)
Absolutely not. It is for more than 200 M records and I need that to work in SSIS but struggling to get it.In SQL, I'm using a function which is killing performance.
So do you want to change non-integer values to NULL or do you want to only process rows that have integer values? If it is the second thing then doing it against the source is going to work better than doing it in SSIS.
Again, what version of SQL Server are you using? IF you are on 2012 or higher than doing it in SQL is going to be easier and probably faster than SSIS.
In SQL Server 2012+ you can use one of these queries as your source depending on which result you want (based on your example data):
/* only returns rows that have integers in the value column */
SELECT
*
FROM
[dbo].[Source1]
WHERE
TRY_CONVERT(INT, Source1.Value) IS NOT NULL;
/* replaces non-integers with NULL */
SELECT
S.ID,
TRY_CONVERT(INT, S.Value) AS Value
FROM
dbo.Source1 AS S;
If you need to do it in SSIS I'd probably use a script component in the data flow and use the .NET TryParse function.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
What I posted doesn't use a function and it'll be the best way to do it. If you want to filter rows, you just change the condition to the WHERE clause and avoid pulling every row just to discard them in SSIS.
-
Using below VB Script, the values are being separated to 2 different columns(Numbers and Alphabets). The values I need are only Integers and not with decimals, alphabets..etc in the Numbers columns.
If Row.Numbers_IsNull = False Then
Dim pattern As String = String.Empty
Dim r As Regex = Nothing
pattern = "[^0-9]"
r = New Regex(pattern, RegexOptions.Compiled)
Row.Numbers = Regex.Replace(Row.Numbers, pattern, "")
End If
If Row.Alphabets_IsNull = False Then
Dim pattern As String = String.Empty
Dim r As Regex = Nothing
pattern = "[^[A-Za-z]"
r = New Regex(pattern, RegexOptions.Compiled)
Row.Alphabets = Regex.Replace(Row.Alphabets, pattern, "")
End If
-
You don't need a regex to find out if it is an integer in .NET. You can use INT32.TryParse().
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply