Query Regarding REPLACE function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Regarding REPLACE function

Author

Topic

ambujnema
Starting Member

11 Posts

Posted - 2009-02-26 : 23:38:31

Hi all

I have a problem

by running the query as following

SELECT
REPLACE(

CASE FUD.IsInternationaMail
WHEN 1 THEN 'International Mail'
WHEN 0 THEN ''END

+','+

CASE FUD.IsBulkShipments
WHEN 1 THEN 'Bulk Shipment'
WHEN 0 THEN ''END

+','+

CASE FUD.SpeedNameFileFlag
WHEN 1 THEN 'speed name'
WHEN 0 THEN ''END
+','+

CASE FUD.IsDomesticMail
WHEN 1 THEN 'Domestic Mail'
WHEN 0 THEN ''END
+','+

CASE FUD.SpecialCodingFlag
WHEN 1 THEN 'Special Coding'
WHEN 0 THEN ''END
+','+

Case FUD.InkJetGridInstructions
WHEN 1 THEN 'InkJet Grid Instructions'
WHEN 0 THEN ''END

+','+

CASE FUD.MiscellaneousInstructions

WHEN 1 THEN 'Miscellaneous Instructions'
WHEN 0 THEN ''END

+','+

CASE FUD.SuppressionFile
WHEN 1 THEN 'Suppression File'
WHEN 0 THEN ''END

,',,',',') AS 'UploadType',FUD.ClientFileId FROM FileUploadDetails FUD

INNER JOIN ClientFile CF ON FUD.ClientFileId= CF.ClientFileId
INNER JOIN UploadZipFile UZF ON UZF.UploadZipFileId = CF.UploadZipFileId
INNER JOIN ContactTitle CT ON CT.TitleId = UZF.TitleId AND CT.Accountid = UZF.AccountID
WHERE CT.contactid=4 AND CF.IsAssociated=1

I am getting the output like

Uploadtype(Column Name)

(firstRow)
International Mail,,Domestic Mail,InkJet Grid Instructions,Miscellaneous Instructions,

(Second Row)
,,Special Coding,InkJet Grid Instructions,Miscellaneous Instructions,

Now My requirment is like

I want to remove the , which is comming twice and thrice

when ,,, are comming continuously it is replacing ,, to , and rest one is remaing

so finally it is comming as ,,

and in second case when ,, are comming continuously ,it is replacing that with , and works fine

Please tell me the approach which will Remove this

what should be the query

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-27 : 01:40:51

try this

SELECT
		(

		CASE FUD.IsInternationaMail
		WHEN 1 THEN 'International Mail' +','
		WHEN 0 THEN ''END

		+

		CASE FUD.IsBulkShipments
		WHEN 1 THEN 'Bulk Shipment' +','
		WHEN 0 THEN ''END

		+

		CASE FUD.SpeedNameFileFlag
		WHEN 1 THEN 'speed name' +','
		WHEN 0 THEN ''END
		+

		CASE FUD.IsDomesticMail
		WHEN 1 THEN 'Domestic Mail' +','
		WHEN 0 THEN ''END
		+

		CASE FUD.SpecialCodingFlag
		WHEN 1 THEN 'Special Coding' +','
		WHEN 0 THEN ''END
		+

		Case FUD.InkJetGridInstructions
		WHEN 1 THEN 'InkJet Grid Instructions' +','
		WHEN 0 THEN ''END

		+

		CASE FUD.MiscellaneousInstructions

		WHEN 1 THEN 'Miscellaneous Instructions' +','
		WHEN 0 THEN ''END

		+

		CASE FUD.SuppressionFile
		WHEN 1 THEN 'Suppression File'
		WHEN 0 THEN ''END

		) AS 'UploadType',FUD.ClientFileId 

FROM FileUploadDetails FUD
INNER JOIN ClientFile CF ON FUD.ClientFileId= CF.ClientFileId
INNER JOIN UploadZipFile UZF ON UZF.UploadZipFileId = CF.UploadZipFileId
INNER JOIN ContactTitle CT ON CT.TitleId = UZF.TitleId AND CT.Accountid = UZF.AccountID
WHERE CT.contactid=4 AND CF.IsAssociated=1

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

ambujnema
Starting Member

11 Posts

Posted - 2009-02-27 : 02:10:22

Hi thanks for taking the intrest

you are removing the REPLACE function here

but i have to use that function for replacing ,, to ,

Please check this .............

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-27 : 03:46:51

have u checked the results for the above query?
There is no need to use REPLACE, first try understand the solution given.

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

ambujnema
Starting Member

11 Posts

Posted - 2009-02-27 : 04:06:52

Hi

my stored Procedure is like

ALTER PROCEDURE [dbo].[usp_GetFileSummaryLatest]
(@XMLContactID NTEXT)
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
DECLARE @ContactId INT
DECLARE @hDoc INT

BEGIN TRY

EXEC sp_xml_preparedocument @hDoc OUTPUT,@XMLContactID
SELECT @ContactId = [XmlDoc].[ContactId]
FROM OPENXML (@hDoc, 'Contacts/Contact', 2)
WITH
(
[ContactId] INT
) XmlDoc

-- Remove the XML Document
EXECUTE sp_xml_removedocument @hDoc;

SELECT
(

CASE FUD.IsInternationaMail
WHEN 1 THEN 'International Mail' +','
WHEN 0 THEN ''END

+

CASE FUD.IsBulkShipments
WHEN 1 THEN 'Bulk Shipment' +','
WHEN 0 THEN ''END

+

CASE FUD.SpeedNameFileFlag
WHEN 1 THEN 'speed name' +','
WHEN 0 THEN ''END
+

CASE FUD.IsDomesticMail
WHEN 1 THEN 'Domestic Mail' +','
WHEN 0 THEN ''END
+

CASE FUD.SpecialCodingFlag
WHEN 1 THEN 'Special Coding' +','
WHEN 0 THEN ''END
+

Case FUD.InkJetGridInstructions
WHEN 1 THEN 'InkJet Grid Instructions' +','
WHEN 0 THEN ''END

+

CASE FUD.MiscellaneousInstructions
WHEN 1 THEN 'Miscellaneous Instructions' +','
WHEN 0 THEN ''END

+

CASE FUD.SuppressionFile
WHEN 1 THEN 'Suppression File'
WHEN 0 THEN ''END

) AS 'UploadType',FUD.ClientFileId

FROM FileUploadDetails FUD
INNER JOIN ClientFile CF ON FUD.ClientFileId= CF.ClientFileId
INNER JOIN UploadZipFile UZF ON UZF.UploadZipFileId = CF.UploadZipFileId
INNER JOIN ContactTitle CT ON CT.TitleId = UZF.TitleId AND CT.Accountid = UZF.AccountID
WHERE CT.contactid=4 AND CF.IsAssociated=1

SELECT ClF.ClientFileId AS 'ClientFileId', UZF.[FileName] AS 'UploadFileName', CLF.[FileName] AS 'IndividualFileName',
ClF.CreatedDate 'Upload Date and Time', Acc.AccountName,T.TitleName,
cte.UploadType,
CASE ClF.IsWebUpload
when 1 THEN 'Web Upload'
WHEN 0 THEN '' END AS 'Upload Method' ,

CASE ClF.IsReplacementFile
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'Np' END AS 'Rep.File',

UZF.UploadZipFileId

FROM ClientFile ClF
INNER JOIN UploadZipFile UZF
ON UZF.UploadZipFileId =ClF.UploadZipFileId
INNER JOIN Title T
ON T.TitleId = UZF.TitleId
INNER JOIN Account ACC
ON ACC.AccountId = UZF.AccountId
INNER JOIN ContactTitle CT
ON CT.TitleId = UZF.TitleId AND CT.AccountId = UZF.AccountId
RIGHT JOIN tbl cte
ON cte.ClientFileId = ClF.ClientFileId

WHERE CT.contactid=@ContactId AND ClF.IsAssociated=1 ;

I tried the query given by u

still

i am getting the out put like

upload type

,,Domestic Mail,,
International Mail,,Domestic Mail,InkJet Grid Instructions,Miscellaneous Instructions,
,,Special Coding,InkJet Grid Instructions,Miscellaneous Instructions,

Please tell where i am worng

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources