Thanks Greg just what I was after.

-----Original Message-----
From: frazerg@t... [mailto:frazerg@t...]
Sent: 28 May 2002 07:17
To: sql language
Subject: [sql_language] Re: String Manipulation


Richard
to eliminate leading nulls and space you could use the ltrim fuction. To 
eliminate trailing spaces you could you the rtrim function.
Here is you code rewritten with the ltrim fucntion and the length of 51 
being captured.
I have commented our the -6 and -26 as these are redundant (just chewing 
more CPU cycles)
/******************************
select @NewFile = 'BM9T1.BM9_TT01__.gsm.NCDR.0000000646.20020520221816'
if len(Ltrim(@NewFile)) =51
BEGIN
	select @File = left(substring(Ltrim(@NewFile),7,len(Ltrim
(@NewFile))/*-6*/),14) +
	substring(Ltrim(@NewFile),27,len(Ltrim(@NewFile))/*-26*/)
	select @file
END


******************************/

Regards 


Greg Frazer
> I am changing this

XYZT1.XYZ_TT01__.gsm.NCDR.0000000646.20020520221816 

to this:

BM9_TT01__.gsm0000000646.20020520221816

I have knocked this up in query analyser which gives me the result I am
after.

declare @file varchar(60)
declare @NewFile varchar(60)

select @NewFile = 'BM9T1.BM9_TT01__.gsm.NCDR.0000000646.20020520221816'
select @File = left(substring(@NewFile,7,len(@NewFile)-6),14) +
substring(@Newfile,27,len(@NewFile)-26)
select @file

I was just after some critique to possibly highlight areas I might have
overlooked.

Notes:

There also other file types where the length differs and these strings 
would
be dealt with differently this file will always have a length of 51, that 
is
how I will catch it. 

I am also worried about leading spaces and nulls, ideally the final 
function
should be a catch all scenario.  

Anything appreciated.

Thanks 

Richard




NOTICE AND DISCLAIMER:
This email (including attachments) is confidential.  If you have received
this email in error please notify the sender immediately and delete this
email from your system without copying or disseminating it or placing any
reliance upon its contents.  We cannot accept liability for any breaches of
confidence arising through use of email.  Any opinions expressed in this
email (including attachments) are those of the author and do not 
necessarily
reflect our opinions.  We will not accept responsibility for any 
commitments
made by our employees outside the scope of our business.  We do not warrant
the accuracy or completeness of such information.




NOTICE AND DISCLAIMER:
This email (including attachments) is confidential.  If you have received
this email in error please notify the sender immediately and delete this
email from your system without copying or disseminating it or placing any
reliance upon its contents.  We cannot accept liability for any breaches of
confidence arising through use of email.  Any opinions expressed in this
email (including attachments) are those of the author and do not necessarily
reflect our opinions.  We will not accept responsibility for any commitments
made by our employees outside the scope of our business.  We do not warrant
the accuracy or completeness of such information.