Tuesday, March 6, 2012
About unicode string comparison
Can anyone here explain why the 2 Japanese strings are equal?
select case when N'あきよ' = N'ア_ヨ' then 'true' else 'false' end
select convert(varbinary,N'あきよ')
select convert(varbinary,N'ア_ヨ')
Thanks,
JamesJames,
They are equal under a Kana-insensitive collation. If you need to
distinguish them, use a Kana-sensitive collation. Equality of strings
is based on the rules of the collation in effect, not the strings' binary
representations.
select
case when N'あきよ' = N'ア_ヨ' collate Latin1_General_CS_AS_KS_WS
then 'true' else 'false'
end
This returns 'false'
Steve Kass
Drew University
James Ma wrote:
>Hi,
>Can anyone here explain why the 2 Japanese strings are equal?
>select case when N'あきよ' = N'ア_ヨ' then 'true' else 'false' end
>
>select convert(varbinary,N'あきよ')
>select convert(varbinary,N'ア_ヨ')
>Thanks,
>James
>|||Thank you so much.
"Steve Kass" wrote:
> James,
> They are equal under a Kana-insensitive collation. If you need to
> distinguish them, use a Kana-sensitive collation. Equality of strings
> is based on the rules of the collation in effect, not the strings' binary
> representations.
> select
> case when N'あきよ' = N'ア_ヨ' collate Latin1_General_CS_AS_KS_WS
> then 'true' else 'false'
> end
> This returns 'false'
> Steve Kass
> Drew University
> James Ma wrote:
>
>
Sunday, February 19, 2012
about search query
1. ignore whether the search keyword is Upper case or lower Case
2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea
1. ignore whether the search keyword is Upper case or lower Case
SQL Queries are case insensitive.
2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea
No idea. I am unable to understand your question.
|||
sorry for my unclear idea!
What I want to do:
If people input the word in (a), the ColA contain word (b) will also be selected.
For example, select count(*) from tableA where ColA like '%compare%' , or sth like that
(a) (b)
1.CASW Casw
2.compare comparing
1. Since the keyword is in '', the case is sensitive, so Casw should not be select
2. Similary, since search compare will include provide result which have comparing,
Thx
Table A
Col_1 Col_hobby
Tom football, piano, reading, playing computer game, compare
Sam Football, piano, reading, comparing
When people use the query
select * from tableA where Col_hobby like '%Football%'
Only Tom record is selected, since the string in '' is sensitive the case. Any way to make it insensitve?
select * from tableA where Col_hobby like '%compare%'
Any way to make the query get the sam record?
|||Use the SQL Lower function to make the field in lower case and the searched keyword in lower case, that way you can get the desired result.|||you should get both records without having to worry about case.|||
ndinakar wrote:
you should get both records without having to worry about case.
Are you sure about that? I have tried several times a query like this:
select * from tableA where Col_hobby like '%Football%'
and it doesnt return the rows where col_hobby has the value 'football', but it does return the values with 'Football'|||
DECLARE @.tTABLE (colAvarchar(10), colBvarchar(100))
INSERTINTO @.tVALUES ('Tom','FOOTBALL,PIANO')
INSERTINTO @.tVALUES ('Sam','football,piano,volleyball')
SELECT*FROM @.tWHERE colBLIKE'%football%'
-------------
(1 row(s) affected)
(1 row(s) affected)
colA colB
---- ------------------------------
Tom FOOTBALL,PIANO
Sam football,piano,volleyball
(2 row(s) affected)
|||Thanks for the followup ndinkar. I always that my query didnt worked.Saturday, February 11, 2012
about efficiency(rephrased)
could you tell me which case is more efficiency?(my tables have no index)
And does it has any else case more efficiency?
case1:
"select sum(Invoice_Production.Quantity) from Invoice_Production,(select
[dat_Item].ItemCode from [dat_Item],(select [dat_MachineType].MachineTypeID
from [dat_MachineType]"&subQuery&") as T3 where [dat_Item].MachineTypeID =
T3.machinetypeid) as T1,(select [Invoice].InvoiceNo from Invoice,(select
[users].user_id from [users] where [Users].User_ID = '"& rs2(0) &"') as T4
where T4.User_ID = invoice.dealerno and Invoice.Cyear >= "&startYear&" and
Invoice.Cyear <= "&endYear&" and Invoice.Cmonth >= "&startMonth&" and
Invoice.Cmonth <= "&endMonth&") as T2 where invoice_production.ItemCode =
T1.ItemCode and T2.invoiceno = invoice_production.invoiceno"
case2:
"select sum(Invoice_Production.Quantity) from
[Invoice_Production],[Invoice],[dat_MachineType],[dat_Item],[users] where
[users].user_id = [invoice].DealerNo and [dat_Item].ItemCode =
[Invoice_Production].ItemCode and [dat_Item].MachineTypeID =
[dat_MachineType].MachineTypeID and [Invoice_Production].InvoiceNo =
[Invoice].InvoiceNo and [Users].User_ID = '"& rs2(0) &"' and Invoice.Cyear
Quote:
Originally Posted by
>= "&startYear&" and Invoice.Cyear <= "&endYear&" and Invoice.Cmonth >=
"&startMonth&" and Invoice.Cmonth <= "&endMonth&""
Thanks for any help
Yicongyicong wrote:
Quote:
Originally Posted by
could you tell me which case is more efficiency?(my tables have no index)
Add some indexes!
Quote:
Originally Posted by
And does it has any else case more efficiency?
I would write it as follows, for clarity:
select sum(Invoice_Production.Quantity)
from [Invoice_Production]
join [Invoice] on [Invoice].InvoiceNo = [Invoice_Production].InvoiceNo
join [users] on [users].user_id = [invoice].DealerNo
join [dat_Item] on [dat_Item].ItemCode = [Invoice_Production].ItemCode
join [dat_MachineType] on [dat_MachineType].MachineTypeID =
[dat_Item].MachineTypeID
where [Users].User_ID = '"& rs2(0) &"'
and Invoice.Cyear between "&startYear&" and "&endYear&"
and Invoice.Cmonth between "&startMonth&" and "&endMonth&"
Also, you can remove dat_Item and dat_MachineType entirely (unless you
might have null values with non-zero quantities, and want to exclude
those from the total).|||yicong (cici-lool@.163.com) writes:
Quote:
Originally Posted by
could you tell me which case is more efficiency?(my tables have no index)
And does it has any else case more efficiency?
Why not benchmark instead? Without access to the data, it's difficult
to say what is going to happen.
Anyway, without indexes nothing will be effecient.
Furthermore you should not interpolate parameter values into the string
but use parameterised commands instead, not the least if you are interested
in performance.
I'm sorry, but I gave trying to understand your first query when I came
to this "subquery" that I don't know what it is.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Thursday, February 9, 2012
About CASE and WHERE
SELECT CARTERA_PROV.cCAJA,
CAJA.sCAJA,
CARTERA_PROV.fEMISION,
CARTERA_PROV.fVENCIMIENTO
FROM
CARTERA_PROV,
ANEXO,
TIPO_DOCUMENTO,
CAJA,
CONCEPTO_GASTO,
MOVIMIENTO_CAJA
WHERE
case CARTERA_PROV.cCONCEPTO_CARTERA
when '38' then CARTERA_PROV.gTIPO_PRESTAMO
else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
end = #ANY VALUE#
AND
(...)
*Whatever the value I put instead of #ANY VALUE#, the query ALWAYS analyze
TRUE all this sentence:
case CARTERA_PROV.cCONCEPTO_CARTERA
when '38' then CARTERA_PROV.gTIPO_PRESTAMO
else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
end = #ANY VALUE#
*Can someone explain me that please? I don't think is a bug.
Regards,
Ral La TorreHmm. It seems that what you have should work fine.
Here is an example that shows the results. The query correctly returns the
1st and 4th rows but does not return the 2nd and 3rd rows.
CREATE TABLE TestTable(column1 int, column2 int, column3 int)
GO
INSERT INTO TestTable VALUES(1,2,5)
INSERT INTO TestTable VALUES(2,2,5)
INSERT INTO TestTable VALUES(3,5,5)
INSERT INTO TestTable VALUES(4,2,2)
GO
select *
from TestTable
WHERE CASE column1
WHEN 1 THEN column2
ELSE column3
END = 2
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Raul La Torre" wrote:
> *I have a Query like this:
> SELECT CARTERA_PROV.cCAJA,
> CAJA.sCAJA,
> CARTERA_PROV.fEMISION,
> CARTERA_PROV.fVENCIMIENTO
> FROM
> CARTERA_PROV,
> ANEXO,
> TIPO_DOCUMENTO,
> CAJA,
> CONCEPTO_GASTO,
> MOVIMIENTO_CAJA
> WHERE
> case CARTERA_PROV.cCONCEPTO_CARTERA
> when '38' then CARTERA_PROV.gTIPO_PRESTAMO
> else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
> end = #ANY VALUE#
> AND
> (...)
>
> *Whatever the value I put instead of #ANY VALUE#, the query ALWAYS analyze
> TRUE all this sentence:
> case CARTERA_PROV.cCONCEPTO_CARTERA
> when '38' then CARTERA_PROV.gTIPO_PRESTAMO
> else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
> end = #ANY VALUE#
>
> *Can someone explain me that please? I don't think is a bug.
> Regards,
> Raúl La Torre
>
>|||Just guessing, but try putting your case statement inside parenthesis.
Maybe the entire statement is not parsing as a whole and this will force it
to be handled as you desire. Adding in parentheses and some indentation can
make the code more readable anyway.
However,
I don't see why case is needed, or even useful here. I think an OR will be
easier to follow, more standard (i.e. not proprietary), portable, and more
efficient (I may be wrong here, still learning my way around the SQL tuning
process).
(
(CARTERA_PROV.gTIPO_PRESTAMO = #ANY VALUE# and
CARTERA_PROV.cCONCEPTO_CARTERA = 38)
or
(MOVIMIENTO_CAJA.gTIPO_PRESTAMO = #ANY VALUE# and
CARTERA_PROV.cCONCEPTO_CARTERA <> 38)
)
"Raul La Torre" <raul_la_torre@.hotmail.com> wrote in message
news:usHr%23l8GGHA.3896@.TK2MSFTNGP15.phx.gbl...
> *I have a Query like this:
> SELECT CARTERA_PROV.cCAJA,
> CAJA.sCAJA,
> CARTERA_PROV.fEMISION,
> CARTERA_PROV.fVENCIMIENTO
> FROM
> CARTERA_PROV,
> ANEXO,
> TIPO_DOCUMENTO,
> CAJA,
> CONCEPTO_GASTO,
> MOVIMIENTO_CAJA
> WHERE
> case CARTERA_PROV.cCONCEPTO_CARTERA
> when '38' then CARTERA_PROV.gTIPO_PRESTAMO
> else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
> end = #ANY VALUE#
> AND
> (...)
>
> *Whatever the value I put instead of #ANY VALUE#, the query ALWAYS analyze
> TRUE all this sentence:
> case CARTERA_PROV.cCONCEPTO_CARTERA
> when '38' then CARTERA_PROV.gTIPO_PRESTAMO
> else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
> end = #ANY VALUE#
>
> *Can someone explain me that please? I don't think is a bug.
> Regards,
> Ral La Torre
>