Derived column help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

Derived column help needed

Author

Topic

Andre1802
Starting Member

4 Posts

Posted - 2014-11-19 : 07:26:54

Hi

I ma struggling getting the following case statement into the derived column format

CASE
WHEN SUBSTRING(REPLICATE('0', 9 - LEN(Data)) + CAST(CYCLE_YYYYMM AS VARCHAR(9)), 4, 6) IS NULL
THEN 0
ELSE RTRIM(SUBSTRING(REPLICATE('0', 9 - LEN(Data)) + CAST(Data AS VARCHAR(9)), 4, 6))
END

Can anyone help please

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 09:14:20

what are you getting with this case expression (it's not a statement) and what do you want to get? Please post examples.

Andre1802
Starting Member

4 Posts

Posted - 2014-11-20 : 05:07:45

Please note I need the derived column script syntax for SSIS

Sorry here is the corrected code

CASE
WHEN SUBSTRING(REPLICATE('0', 9 - LEN(Data)) + CAST(Data AS VARCHAR(9)), 4, 6) IS NULL
THEN 0
ELSE RTRIM(SUBSTRING(REPLICATE('0', 9 - LEN(Data)) + CAST(Data AS VARCHAR(9)), 4, 6))
END

Here are two examples for the data in Data
row 1 - 1201303
row 2 - 123456789
row 3 - null
row 4 - 12345678
the first row result would be 201303
row 2 result would be 456789
row 3 result would be 0
row 4 result would be 345678

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources