HELP! Strange behavior using t-sql REPLACE function

I am seeing very odd behavior with the t-sql REPLACE funtion.

I am trying to wipe out the 'T' character (unicode value 222) with a space character (' ' unicode value 32) and it appears to be replacing occurrences of 'th' together as well. Here are some selects to show this behavior:

select nchar(unicode('T'))
select nchar(unicode(' '))
select unicode('T')
select unicode(' ')
select replace('Urban & Ethnic', nchar(unicode('T')), nchar(unicode(' ')))
select replace('Urban T Ethnic', nchar(unicode('T')), nchar(unicode(' ')))
select replace('Urban & Ethnic', nchar(222), nchar(32))
select replace('Urban T Ethnic', nchar(222), nchar(32))
select replace('Urban & Ethnic', 'T', ' ')
select replace('Urban T Ethnic', 'T', ' ')

NOTICE that the last two statements:

select replace('Urban & Ethnic', 'T', ' ') - RESULT 'Urban & Ethnic'
select replace('Urban T Ethnic', 'T', ' ') - RESULT 'Urban Ethnic'

...work properly, while the other ones dont:

select replace('Urban & Ethnic', nchar(unicode('T')), nchar(unicode(' '))) - RESULT 'Urban & E nic'
select replace('Urban T Ethnic', nchar(unicode('T')), nchar(unicode(' '))) - RESULT 'Urban E nic'
select replace('Urban & Ethnic', nchar(222), nchar(32)) - RESULT 'Urban & E nic'
select replace('Urban T Ethnic', nchar(222), nchar(32)) - RESULT 'Urban E nic'

This function ONLY seems to work with the character representation, any attempt to convert an integer value to a character representation fails miserably...

Please advise, and thanks for your help in advance.

It seems that the collation the active database uses treats "th" and "T" as the same character. To fix this, change the database collation to something more appropriate, or use tricks like the following in your queries:

select replace(N'Urban T Ethnic' COLLATE Latin1_General_BIN, N'T', N' ')

By forcing the collation to BIN (binary), a binary comparison is done, which doesn't do fancy character-equivilancy stuff.

-Ryan

|||

Thanks!

sp_helpsort reveals the default collation = Latin1-General, blah, blah, blah, etc...

I even tried to convert EVERYTHING to binary var types within the query and it STILL came up with the wrong result.

Thanks again for your help. I didn't even consider changing the COLLATION type in the query itself...

sql

Help! Problem with DateTime.Now and SQLServer 2005

I am in the proces of upgrading from access to SQL Server 2005, and I
am having a strange problem. When I set the value a column in a data
row to DateTime.Now and save it to the server the date is two days in
the future! This happens for both typed and untyped datasets.
ex DataTable dt=new DataTable("Test")
...
DataRow row = dt.Rows[0];
row["testDate"]=DataTime.Now;
...
dbAdapter.Update(dt);
the date saved will be exactly two days in the future.
The same code worked fine with Access, also I am using the OleDb
provider for SqlServerI know this is a dumb question, but what is the date and time set to on
the SLQ Server?|||Nice thought but no dice the server date time is correct|||Hello BetaD,
A couple of quick questions:
a. You did check that the local system (where the code is running) is correc
t?
b. If you're writing .NET code, are you using SqlClient or OleDbClient? If
you're using OleDbClient, why?
c. Is the datetime off in the dataset only or does it saved incorrectly to
SQL server only or is it wrong in both places?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

Help! Issues with Export to Excel

Hi,
I need to export my reports to Excel, and I've encountered strange layout problems, as below.
Problem 1: Looks ok in report, looks crazy in Excel
----
I understand that data regions within table and matrices are not supported (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp).. so I have a matrix in a rectangle(instead of a table), and this rectangle within a list. Report generates this fine,.. nice and neat.., butonce exported to Excel, the layout is messy and unintelligible. One report column can be represented by 1 and some even 10 cells. Does anyone know what is the cause of this? Perhaps the use of lists?
Problem 2: What's #NAME?
--
I have a column X in report that a calculated value, and formula is
=(ReportItems!textbox213.Value / ReportItems!textbox211.Value) ...
textbox213 and textbox211 both have values from sums of other field items. So column X has a proper value when generated, but once exported, it says #NAME in the Excel column (error i suppose). When I click on #NAME, it says =(_146/_144) <-- what does this mean?
I would really appreciate anyone's help on this, since i've spend loads of time (too much!) on this.. Seems like what I see in the report is not what I get in Excel! Anyway, thank you in advance.
Best regards,
Julie
--
Message posted via http://www.sqlmonster.comIt's recommended to use tables rather than rectangles and lists when
exporting to Excel. As the link you provided describes, you get
unpredictable results when using anything other than tables or matrixes.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"JTay via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d285b020b8244ddeb4394efec2c3fb23@.SQLMonster.com...
> Hi,
> I need to export my reports to Excel, and I've encountered strange layout
> problems, as below.
> Problem 1: Looks ok in report, looks crazy in Excel
> ----
> I understand that data regions within table and matrices are not supported
> (see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp)..
> so I have a matrix in a rectangle(instead of a table), and this rectangle
> within a list. Report generates this fine,.. nice and neat.., butonce
> exported to Excel, the layout is messy and unintelligible. One report
> column can be represented by 1 and some even 10 cells. Does anyone know
> what is the cause of this? Perhaps the use of lists?
> Problem 2: What's #NAME?
> --
> I have a column X in report that a calculated value, and formula is
> =(ReportItems!textbox213.Value / ReportItems!textbox211.Value) ...
> textbox213 and textbox211 both have values from sums of other field items.
> So column X has a proper value when generated, but once exported, it says
> #NAME in the Excel column (error i suppose). When I click on #NAME, it
> says =(_146/_144) <-- what does this mean?
> I would really appreciate anyone's help on this, since i've spend loads of
> time (too much!) on this.. Seems like what I see in the report is not what
> I get in Excel! Anyway, thank you in advance.
> Best regards,
> Julie
> --
> Message posted via http://www.sqlmonster.com|||I can't use tables to encapsulate the matrix. If I do put the matrix within the table, it would say "Data Regions within table/matrix cells are ignored" on Excel when exported. This is a well known issue and is currently not supported, even in SP1.
However, I managed to get it to look slightly better in Excel, but after *much* manipulation on the alignment of the matrices and lists...
--
Message posted via http://www.sqlmonster.com|||Okay, I get it, you're right, there's no easy answer -- just lots of
tweaking layout.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"JTay via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7e935d41037d4da694ce1277e681894a@.SQLMonster.com...
>I can't use tables to encapsulate the matrix. If I do put the matrix within
>the table, it would say "Data Regions within table/matrix cells are
>ignored" on Excel when exported. This is a well known issue and is
>currently not supported, even in SP1.
> However, I managed to get it to look slightly better in Excel, but after
> *much* manipulation on the alignment of the matrices and lists...
> --
> Message posted via http://www.sqlmonster.com

Help! Computed Column

I have a strange behavoiur
adding a computed column with the formulae (100 - 38 / 41 * 100)
gives a result in excel as 7.317073171 (correct) but in an sql column it
always returns 100
(the acutal formulae would be (100 - [FIELD] / 41 * 100)
any ideas?
Regards
Toby RileyToby
What's the datatype of [FIELD] you defined in SQL Server?
"TobyRiley" <TobyRiley@.discussions.microsoft.com> wrote in message
news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com...
>I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||>> I have a strange behavoiur
adding a computed column with the formulae (100 - 38 / 41 * 100) <<
Integer math versus decimal math.|||The column might be an integer column
Use it this way.
100 - ([FIELD] *1.0)/ 41 * 100
Hope this helps.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"TobyRiley" wrote:

> I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||Try using "41.0", as (100 - [FIELD] / 41.0 * 100)
Martin C K Poon
Senior Analyst Programmer
====================================
"TobyRiley" <TobyRiley@.discussions.microsoft.com> bl
news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com g...
> I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||If [FIELD] in an integer datatype, then anything less than 41 divided by 41
will be truncated to 0, multiplied by 100 it is STILL 0. Subtract 0 from 100
and you have 100. One of the value in the division must be a datatype that
supports decimal digits.
HTH
Kalen Delaney, SQL Server MVP
"TobyRiley" <TobyRiley@.discussions.microsoft.com> wrote in message
news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com...
>I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||Thank for all your quick replies, that's fixed it.
--
Regards
Toby Riley
"Kalen Delaney" wrote:

> If [FIELD] in an integer datatype, then anything less than 41 divided by 41
> will be truncated to 0, multiplied by 100 it is STILL 0. Subtract 0 from 1
00
> and you have 100. One of the value in the division must be a datatype tha
t
> supports decimal digits.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "TobyRiley" <TobyRiley@.discussions.microsoft.com> wrote in message
> news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com...
>
>