CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Eric Klaus
Eric Klaus

Reputation: 955

time format and string format

I'm using SQL server to form file names to Ms Word documents. The format is like this clientnumber_date_time_clientname.doc.

Problem is Ms Word doesn't accept 10:19:23 time format as file name. How to change it into 10-19-23 format?

In some cases clent name is something like this: the "Coca Cola" company. Apparently, Ws Word doesn't accept "(double quotation) mark as file name. I' ve tried to change it into '(single quotation) mark via 'replace' , but it didn't work out. How to change "xxx" into 'xxx'?

Thanks in advance

here is part of that code 

convert(varchar,e.ExternalID)+'_'+left(convert(varchar,@Now,120),10) +'uved'+'_'+convert(varchar,a.OwnerName)+'.doc' as NameFile

Upvotes: 1

Views: 74

Answers (2)

PSK
PSK

Reputation: 17943

Try like following code.

DECLARE @CName varchar(100)
DECLARE @CNumber varchar(10)
SET @CNumber ='1234'
SET @CName = '"Coca Cola"'
SELECT @CNumber + '_' 
                + REPLACE(REPLACE(CONVERT(VARCHAR(19), GETDATE(), 120),':','-'),' ' ,'_') 
                + '_' 
                + REPLACE(@CName,'"','''') 
                + '.doc'

Run this query and you will get exactly the same filename what you are expecting.

Upvotes: 1

Atomic Star
Atomic Star

Reputation: 5507

You can use REPLACE as in:

SELECT REPLACE([column],'oldstring','newstring')
FROM [MyTable]

Upvotes: 0

Related Questions