Removing a character from file name

[lasd]

Hi

I was hoping someone could help me. I have written some SQL code to extract data from some tables. The data has been extracted but then i want to copy this data to a different file. The problem is that one of the fields(path field) contains some characters that i do not want. for eg. 2D#201!.TIF. i do not want to see the hash here. i want to remove and delete the hash from the original file name when i am copying it over to the new file. Is this possible and do I need an if-then-else statement.... here is the code i wrote to copy over the file

select 'Copy ' || profile.docserver_loc || profile.path || TRIM(components.path)
|| ' ' || TRIM(components.path)

it is on the second TRIM(components.path) that i need to delete the hash in the file name.
thanks a million in advance for the help.
lasd

 

[DrSql]

Why dont you look in to Replace.. You can create a custon function using that.
Here is a sample function
[tt]
CREATE function dbo.fn_remove_chars(
@p_name_text varchar(255)
)
returns varchar(255)
as
begin
set @p_name_text = REPLACE( @p_name_text,',','')
set @p_name_text = REPLACE( @p_name_text,'-','')
set @p_name_text = REPLACE( @p_name_text,' ','')
set @p_name_text = REPLACE( @p_name_text,' *','')
set @p_name_text = REPLACE( @p_name_text,"'",'')
set @p_name_text = REPLACE( @p_name_text,"'!",'')
set @p_name_text = REPLACE( @p_name_text,"'#",'')
-- put multiple spaces to a single space
set @p_name_text = REPLACE( @p_name_text,' ','')
return @p_name_text
end
[/tt]
SELECT dbo.fn_remove_chars('2D#201!.TIF','!','')
GO

Here is the result set:

------------
2D201.TIF









Dr.Sql
Good Luck.