Sunday, March 11, 2012
Error In Replace With Spaces
I'm trying to put a space after . and , in varchar fields.
I've tried
UPDATE Resultados
SET RespuestaAbierta = REPLACE(RespuestaAbierta, ',', ', ')
but nothing happened
What's wrong ??
Thanks and sorry if my english isnt so good.
Eduardo (from Argentina)This works fine for me:
drop table test2
create table test2(f1 varchar(1000))
insert test2 select '1,2,3,4,5,6,7'
select REPLACE(f1, ',', ', ') from test2
update test2 set f1=REPLACE(f1, ',', ', ')
select * from test2|||It seems to work fine for me, using:SELECT Replace('1,2,3,4,5,6,7,8,9', ',', ', ')Maybe the character you are looking for only looks like it is a comma.
-PatP|||Trabajos para m
DECLARE @.RespuestaAbierta varchar(50)
SELECT @.RespuestaAbierta = 'Brett,Kaiser'
SELECT REPLACE(@.RespuestaAbierta, ',', ', ')|||I can't understand,
this works fine ...
drop table test2
create table test2(f1 varchar(4000))
insert test2 select '1,2,3,4,5,6,7'
insert test2 select 'ninguna en especial,me la recomendo mi hermano.'
select REPLACE(f1, ',', ', ') from test2
update test2 set f1=REPLACE(f1, ',', ', ')
select * from test2
1, 2, 3, 4, 5, 6, 7
ninguna en especial, me la recomendo mi hermano.
but this one doesn't
select respuestaabierta,
REPLACE(respuestaabierta, ',', ', ')
from resultados
where idresultado = 1244591
ninguna en especial,me la recomendo mi hermano. ninguna en especial,me la recomendo mi hermano.
The field respuestaabierta is varchar(4000) null
Thanks for your answers !|||I remeber...the answer is that the comma in your column is not a comma!
That bugged us for a while once...
select char(130), ascii(',')
Do This
DECLARE @.x varchar(80)
SELECT @.x = 'This is not a comma' + CHAR(130) + ' even though it looks like one'
SELECT @.x
-- Then do
select CHARINDEX(CHAR(130),respuestaabierta)
from resultados
where idresultado = 1244591
-- If it's not 0, then that's what it is...
And let us know...|||Thanks, Brett
but that's not the problem :
select CHARINDEX(CHAR(130),respuestaabierta) , CHARINDEX(CHAR(44),respuestaabierta)
from resultados
where idresultado = 1244591
Output:
0 20
I've noticed that the table has
Collation = SQL_Latin1_General_CP1_CI_AS
and, with Collation = databasedefault works fine
what does it mean ?
Thanks|||Damn...I thought that might have been it...
Can you post the DDL of your table?
Do you know how to do that in Enterprise manager?|||Bred,
thanks for everything, I gave up
I did a stored procedure wich updates row by row (instead a single update command).
Thanks again. Eduardo (from Argentina)|||Too bad...I was gonna suggest that you find the position and say
SELECT ASCII(SUBSTRING(col1,n,1))
to see what the damn thing was...
what code did you use?
Friday, February 17, 2012
Error handling with regards to a caculation
I have a report that calculates a students GPA based on two fields in a dataset:
TrmGpaPts/CreditAwd = GPA SQL below
My issue is that the students with 0 (zero) or NULL credits awarded results in a "divide by zero error". How can I place a 0 (zero) in the GPA column when the student has 0 or NULL credits?
Thanks
select
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as Ident,
stugrp_active.graden as Grade,
trnscrpt.graden as Grd_Lvl,
sum(trnscrpt.gradcrawd) as CreditAwd,
round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts
from
dbo.trnscrpt
inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
where
school.schname = @.school and
stugrp_active.graden =@.graden and
trnscrpt.graden = @.trnscrptgraden and
trnscrpt.termc = @.termc
group by
school.schname,
trnscrpt.termc,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname),
trnscrpt.suniq,
stugrp_active.graden,
trnscrpt.graden
order by
School,
Student
When CreditAwd is zero then you don't want to do the division (TrmGpaPts/CreditAward)
You should put something like this in the expression for your report to get the GPA.
=IIf(Fields!CreditAward.Value = 0, "N/A", (Fields!TrmGpaPts.Value) / IIf(Fields!CreditAward.Value = 0, 1, Fields!CreditAward.Value))
|||
Thanks! I see the general direction I need to take.
|||Glad I could help.|||
Using GregSQL's expression from above, I'm getting the error below.
What does this mean to specify the data set scope and where would I do that?
The Value expression for the textbox ‘GPA’ refers to the field ‘CreditAward’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Build complete
|||It's saying that field is not in your dataset. Open your dataset in business intelligence studio (View -> datasets). Right click Report Datasets and click refresh. See if CreditAward is then added to your dataset in that window. If not, then you are not selecting it properly in your SQL query.|||
Thanks. Makes sense once I spotted the typing error.
Error handling with regards to a caculation
I have a report that calculates a students GPA based on two fields in a dataset:
TrmGpaPts/CreditAwd = GPA SQL below
My issue is that the students with 0 (zero) or NULL credits awarded results in a "divide by zero error". How can I place a 0 (zero) in the GPA column when the student has 0 or NULL credits?
Thanks
select
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as Ident,
stugrp_active.graden as Grade,
trnscrpt.graden as Grd_Lvl,
sum(trnscrpt.gradcrawd) as CreditAwd,
round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts
from
dbo.trnscrpt
inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
where
school.schname = @.school and
stugrp_active.graden =@.graden and
trnscrpt.graden = @.trnscrptgraden and
trnscrpt.termc = @.termc
group by
school.schname,
trnscrpt.termc,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname),
trnscrpt.suniq,
stugrp_active.graden,
trnscrpt.graden
order by
School,
Student
When CreditAwd is zero then you don't want to do the division (TrmGpaPts/CreditAward)
You should put something like this in the expression for your report to get the GPA.
=IIf(Fields!CreditAward.Value = 0, "N/A", (Fields!TrmGpaPts.Value) / IIf(Fields!CreditAward.Value = 0, 1, Fields!CreditAward.Value))
|||
Thanks! I see the general direction I need to take.
|||Glad I could help.|||
Using GregSQL's expression from above, I'm getting the error below.
What does this mean to specify the data set scope and where would I do that?
The Value expression for the textbox ‘GPA’ refers to the field ‘CreditAward’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Build complete
|||It's saying that field is not in your dataset. Open your dataset in business intelligence studio (View -> datasets). Right click Report Datasets and click refresh. See if CreditAward is then added to your dataset in that window. If not, then you are not selecting it properly in your SQL query.|||
Thanks. Makes sense once I spotted the typing error.