0

Below is the sample SQL.

From Source1 tb, I need only whole numbers 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]

ValueNEW needed as output.

0

1 Answer 1

1

I think you're asking about using IsNumeric and CharIndex in your source query and then throwing away the things that are not whole numbers. I would change my source query to be

SELECT 
    S.* 
,   CASE
        WHEN
        -- This filters for only the numeric type of data
        ISNUMERIC(S.Value) > 0
        -- this filters the non whole numbers
        AND CHARINDEX('.', S.Value) = 0
            THEN S.Value
        ELSE NULL
    END AS NewValue
FROM 
    dbo.Source1 AS S;

Otherwise, you're looking at either using a series of Derived Column Components or a Script Component to do the same actions.

References

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.