Replace '' with characters - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Replace '' with characters

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-14 : 10:46:47

Hi. I am trying to replace '' with '%0D%0A'
currently i am getting Admission1:100Admission2:150'%0D%0AAdmission3:200
When i want to get Admission1:100%0D%0AAdmission2:150'%0D%0AAdmission3:200


DECLARE @MSG AS NVARCHAR(MAX)

	SELECT @MSG = COALESCE(@MSG + '%0D%0A', '') +
--	SELECT
	 Cinema_DisplayName + 
	 Replace(LEFT((CONVERT(nvarchar, CAST(sum(admissions) AS money), 1)), LEN((CONVERT(nvarchar, CAST(sum(admissions) AS money), 1))) - 3),',','.') +
	Case Replace(LEFT((CONVERT(nvarchar, CAST(sum(CoolAdmissions) AS money), 1)), LEN((CONVERT(nvarchar, CAST(sum(CoolAdmissions) AS money), 1))) - 3),',','.')
	When 0 then ''  else  'Summer' +   Replace(LEFT((CONVERT(nvarchar, CAST(sum(CoolAdmissions) AS money), 1)), LEN((CONVERT(nvarchar, CAST(sum(CoolAdmissions) AS money), 1))) - 3),',','.')
	END
	--,
   --    Sum(CoolAdmissions) AS CoolAdmissions,	
--CASE
----	when 
	
 --   CHARINDEX('.',(LEFT(sum(Admissions+CoolAdmissions) * 100.0 / (select sum(Admissions+CoolAdmissions) from ZZ_vwpProgressBoard),5)),3) > 0 THEN LEFT(sum(Admissions+CoolAdmissions) * 100.0 / (select sum(Admissions+CoolAdmissions) from ZZ_vwpProgressBoard),5) + '%'
--	ELSE LEFT(sum(Admissions+CoolAdmissions) * 100.0 / (select sum(Admissions+CoolAdmissions ) from ZZ_vwpProgressBoard),4) 	+ '%'
--END as Percentage	
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName
ORDER BY MAX(Cinema_Order)
select @msg

Any help?
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-14 : 10:54:42

Do you have any test data?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-14 : 16:17:21

I can create a table but it's not anything fancy.
Cinema_DisplayName in a nvarchar
admissions is int
CoolAdmissions is int.
Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-14 : 18:28:43

How do you know where to insert '%0D%0A'? I don't see a character that you are replacing it with.

Please show us more data samples.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-15 : 19:45:44

[code] USE [lagetest]
GO

/****** Object: Table [dbo].[ZZ_vwpProgressBoard] Script Date: 11/16/2014 02:42:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ZZ_vwpProgressBoard](
[Cinema_Displayname] [nvarchar](50) NULL,
[admissions] [int] NULL,
[cooladmissions] [int] NULL
) ON [PRIMARY]

GO

[/code]
Data:
Cinema1 123 11
Cinema2 34 0
Cinema3 55 0
Cinema4 33 11

result:
Cinema1123Summer11%0D%0ACinema234%0D%0ACinema355%0D%0ACinema433Summer11
expected result:
Cinema1123%0D%0ASummer11%0D%0ACinema234%0D%0ACinema355%0D%0ACinema433%0D%0ASummer11

P.S. you can comment order by to work this out.

Kristen
Test

22859 Posts

Posted - 2014-11-16 : 04:13:21

You are concatenating three items together with no spaces, and no line break either. I presume these are the places you need to add the linebreak, but I haven't tested it:


	SELECT @MSG = COALESCE(@MSG + '%0D%0A', '') +
--	SELECT
	 Cinema_DisplayName + '%0D%0A' +
	 Replace(LEFT((CONVERT(nvarchar, CAST(sum(admissions) AS money), 1)), LEN((CONVERT(nvarchar, CAST(sum(admissions) AS money), 1))) - 3),',','.') +'%0D%0A' +
	Case Replace(LEFT((CONVERT(nvarchar, CAST(sum(CoolAdmissions) AS money), 1)), LEN((CONVERT(nvarchar, CAST(sum(CoolAdmissions) AS money), 1))) - 3),',','.')
	When 0 then ''  else  'Summer' +  '%0D%0A' + Replace(LEFT((CONVERT(nvarchar, CAST(sum(CoolAdmissions) AS money), 1)), LEN((CONVERT(nvarchar, CAST(sum(CoolAdmissions) AS money), 1))) - 3),',','.')
	END


LEFT((CONVERT(nvarchar, CAST(sum(admissions) AS money), 1)), LEN((CONVERT(nvarchar, CAST(sum(admissions) AS money), 1))) - 3),',','.')

Yuck! Dreadful and inefficient means of getting a thousands separator and no decimals.

Also, you should not use nvarchar on its own, without a size definition, as the default length will let you down one day.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-16 : 11:36:18

Hi.That produces a wrong result also:
Cinema1%0D%0A123%0D%0ASummer%0D%0A11%0D%0ACinema2%0D%0A34%0D%0A%0D%0ACinema3%0D%0A55%0D%0A%0D%0ACinema4%0D%0A33%0D%0ASummer%0D%0A11

I would also appreciate if you saw a more efficient way for the separator but i haven't made that so not sure if they had some specific requirements(person made these departed the company).
Also thanks for the nvarchar tip. Should give it a MAX size? Or since it's gross i should give it 10-15?
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-16 : 17:49:38

SQL Server 2012:

select format(cast(31459.14159 as money), '##,###')

Sql Server 2008:

I often use CROSS APPLY as a sort-of scalar function (without the cost of a real scalar function)

e.g.:


select * from (
SELECT sum(n) sumn
from (values (314159.314159)) v(n)
) _0
cross apply (
	select cast(sumn as money)) _1(sum_money)
cross apply (
	select convert(nvarchar(13), sum_money, 1)) _2(nv_sum)
cross apply (
	select len(nv_sum) ) _3(len_sum)
cross apply (
	select replace(left(nv_sum, charindex('.', nv_sum)-1), ',', '.') ) _4(rep_sum) 
cross apply (
	select replace(left(nv_sum, len_sum-3), ',', '.') ) _5(replen_sum)

If you look at the execution plan, you'll see that it is a straight line. SQL reduced all the cross applies to expressions. Meanwhile, the long expression is reduced to a series of short ones. Also notice that I used charindex to find the decimal point. But I also put in the alternate approach using the length of the data, which produces the same result. Just for comparison.

If you study this approach, you can use it in whole or in part, as you wish.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-11-19 : 07:13:11

Thanks will take that in consideration.
Any chance of a working fix on the replace '' with '%0D%0A' issue?
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources