Sorry for the late response guys. Jonathan, this is exactly what I needed thank you very much

the rules are you take the last 4 digits and the preceeding numbers before the string of zeros. so if account is 12340500080076 , I need 123405 concatenated with 0076...

Hi gvoshol,

you are correct 8 should not be included. Jonathan's answer work's well until I get to accounts that have the variation like 8 on the last account.

Hi Ben, thanks for the response. I have since found out that the last 4 digits are the same in both tables, the variations come from the left side of...

Hi Johnathan,

you are right the values are of variable length so it is very difficult for me to use the left right functions as they only get me the partial...

Hi John,

thank you for the feedback. Fixing the data is not an option available to me. So I tried your solution but it runs too long as I'm applying it...

Thanks John, I was hoping that there is an alternative approach but it looks like I will have to follow your process.

Thank you that is exactly what I was looking for

Thanks for the feedback guys.

HI Guys,

Thanks for the feed back. the max server memory on it is 2147483647. I will try and reduce it and let you know

Thanks a mil guys for your help. both your answers helped me a lot both on reporting services and on query side as well. I can even use this method...

Hi,

Col1 is DT_STR

flat file = 1252 (Ansi - latin I),

Delimited

Header row delimiter {CR}{LF}

non-Unicode

Hi, here's the full detail

CREATE TABLE [dbo].[Table1](

[Category] [nvarchar](max) NULL,

[EXCO] [nvarchar](max) NULL,

[UNIT Number] [float] NULL,

[Staffcount] [float] NULL

)

Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])

Values ('AD', '08', 4514, 60)

Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])

Values...

Yes, that's exactly where the problem is. Those characters have to be part of the output. I believe they are used as delimeters for the system to which I am...

Hi John,

Source = OLE DB Source

Query =

SELECT

'Category, ' + 'EXCO, ' +'UNIT Number, ' +'Staffcount' as col1

union

SELECT

CAST([Category] AS CHAR(6)) +

CAST([EXCO] AS CHAR(2)) +

CAST([UNIT Number]...