x

SSIS : insert characters

Hi all,

I have some membership numbers being imported in an SSIS package. One of our customers has an extra long account number so we would like to split the last two characters off and insert a whitespace, forwardslash, whitespace and the remaining two characters as an identifier. For example :

100418032766000999 would then be

1004180327660009 / 99 .

How can this be done as an SSIS expression? I was thinking maybe a combination of substring/replace maybe in a derived column transform?

Thanks for your help!
more ▼

asked Apr 10, 2012 at 01:30 PM in Default

jhowe gravatar image

jhowe
1.1k 49 57 60

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Yes, I guess you are on the right track but my combination would be LEN and SUBSTRING/RIGHT, something like

LEN(AccountName) > 16 ? SUBSTRING(AccountName,1,16) + " / " + RIGHT(AccountName,2) : AccountName
I guess the code may not be perfect, but should give you the hint.
more ▼

answered Apr 10, 2012 at 02:26 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Thanks Usman that's great what would the equivalent SQL statement be? I just want to test it against the data...
Apr 10, 2012 at 02:38 PM jhowe

I guess that could be

SELECT CASE WHEN LEN(AccountName) > 16 THEN LEFT(AccountName,16) + ' / ' + RIGHT(AccountName,2) ELSE AccountName END 
FROM YOURTABLE
But I am not sure of it either as I cannot test it at the moment.
Apr 10, 2012 at 02:56 PM Usman Butt

For the moment we're assuming full acc numbers are always 18 digits. We may reject numbers greater than this so I've come up with this, i'm guessing this will behave the same way as what you've provided?

SELECT REPLACE(AccNumber, AccNumber,LEFT(AccNumber, 16) + ' / ' + SUBSTRING(MembershipNumber, 16, 2)) FROM dbo.Account
Apr 10, 2012 at 03:16 PM jhowe
I am confused a bit :) Your script have AccNumber and MembershipNumber? Are they both the same? OR the MembershipNumber's last two digits have been added intentionally to make all the accounts eighteen character long (provided with the minimum account length is sixteen characters) with the same pattern? And if you have to reject the accounts more than eighteen characters long then a WHERE clause may be needed. Since the logic is unknown to me I can only guess that your script seems to be to doing the right thing :)
Apr 11, 2012 at 05:06 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

Something to cosider is a mechanism that accepts variable length account numbers, such as :

DECLARE @var_1 VARCHAR(20) = '123456789'
DECLARE @var_2 VARCHAR(3) = ' / '
DECLARE @var_3 VARCHAR(2)

SET @var_3 = RIGHT(@var_1, 2)
SET @var_3 = @var_3 + SUBSTRING(@var_1, 1, ( LEN(@var_1) - 2 ))

SELECT  @var_1 AS [var1] ,
    @var_2 AS [var2] ,
    @var_3 AS [var3] ,
    LEFT(@var_1, ( LEN(@var_1) - 2 )) + @var_2 + @var_3 AS [combo]
This is the TSQL version but you should be able to transfer it to SSIS if you need to
more ▼

answered Apr 10, 2012 at 04:05 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

(comments are locked)
10|1200 characters needed characters left

This is what I came up with in the end thanks everyone for your help!

TRIM(AccountNumber) == "" ? NULL(DT_WSTR,1) : TRIM(SUBSTRING(AccountNumber,1,16) + " / " + SUBSTRING(AccountNumber,16,2))
TRIM(NewAccountNumber) == "" ? NULL(DT_WSTR,1) : TRIM(SUBSTRING(NewAccountNumber,1,16) + " / " + SUBSTRING(NewAccountNumber,17,2))
more ▼

answered Apr 18, 2012 at 10:35 AM

jhowe gravatar image

jhowe
1.1k 49 57 60

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1833
x927
x578

asked: Apr 10, 2012 at 01:30 PM

Seen: 1392 times

Last Updated: Apr 18, 2012 at 10:35 AM