SQL function / statement

[saw15]

Good day ..

Using a select statement, I am pulling back the specific name of a file. With this filename, I need to remove the last four characters ".txt" and replace them with "ECR.txt".

I am looking for the easiest way to do this.

Any help is much appreciated.

 

[PruSQLer]

There are a few ways you could do this depending of course on your RDBMS.

I use DB2 and would do this:

Select translate(FileName, ECR.txt, .txt)

That would replace .txt with ECR.txt.

If FileName is a constant length (say 8 chars before the .txt), you could say:

Select substr(FileName,1,8)||'.txt'

With the double pipes being the concatenation character

I believe with a Microsoft platform you could do:

Select mid(FileName,1,8)**".txt"

 

[tlbroadbent]

In Access 2000, SQL Server 7 and SQL Server 2000 you can use the REPLACE function.

Access 2000:
Select Replace(FileName,".txt","ECR.txt&quot
From table

SQL Server:
Select Replace(FileName,'.txt','ECR.txt')
From table

NOTE: Single quotes should work for Access 2000 also. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[saw15]

Thanks for the info, quick question to Terry.

Will the text be case sensitive?

Example.. several files will end with .txt while others are .TXT.

Will or should this impact the statement?

 

[tlbroadbent]

Access is not case senstive. If you you are using SQL Server, the default collation is not case sensitive but you can set up a case sensitive collation. So the answer depends on your rdms and the setup configuration. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.