Geek Answers Handbook

COLLATE in UDF not working properly

I have a table with a text box. I want to select the lines where the text is in all the headers. This code works as it should and returns ABC:

SELECT txt
FROM (SELECT 'ABC' AS txt UNION SELECT 'cdf') t
WHERE 
txt COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(txt)

then I create UDF (as suggested here ):

CREATE FUNCTION [dbo].[fnsConvert]
(
      @p NVARCHAR(2000) ,
      @c NVARCHAR(2000)
)
RETURNS NVARCHAR(2000)
AS
    BEGIN
        IF ( @c = 'SQL_Latin1_General_CP1_CS_AS' )
            SET @p = @p COLLATE SQL_Latin1_General_CP1_CS_AS
        RETURN @p    
    END

and run it like this (which is equivalent to code for me):

SELECT txt
FROM (SELECT 'ABC' AS txt UNION SELECT 'cdf') t
WHERE 
dbo.fnsConvert(txt, 'SQL_Latin1_General_CP1_CS_AS') = UPPER(txt)

however this returns ABCas well cdf.

Why is this so, and how can I make it work?

PS I need UDF here to be able to call a random comparison with the .Net provider LINQ2SQL.

+6
source share
8 answers

. . :

- , - , :

DECLARE @deflt VARCHAR(100) = 'aBc'; --Latin1_General_CI_AS in my system
DECLARE @Arab VARCHAR(100) = 'aBc' COLLATE Arabic_100_CS_AS_WS_SC;
DECLARE @Rom VARCHAR(100) = 'aBc' COLLATE Romanian_CI_AI

- . :

SELECT [name], system_type_name, collation_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT @deflt AS Deflt, @Arab AS Arab, @Rom AS Rom'
                                          ,N'@deflt varchar(100), @Arab varchar(100),@Rom varchar(100)'
                                          ,0);

/*
name    system_type_name    collation_name
Deflt   varchar(100)        Latin1_General_CI_AS
Arab    varchar(100)        Latin1_General_CI_AS
Rom     varchar(100)        Latin1_General_CI_AS
*/

- "aBc" "ABC"

SELECT CASE WHEN @deflt = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckDefault
      ,CASE WHEN @Arab = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckArab
      ,CASE WHEN @Rom = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckRom

/*CI    CI  CI*/

- !

SELECT CASE WHEN @deflt = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckDefault
      ,CASE WHEN @Arab = 'ABC' COLLATE Arabic_100_CS_AS_WS_SC THEN 'CI' ELSE 'CS' END AS CheckArab
      ,CASE WHEN @Rom = 'ABC' COLLATE Romanian_CI_AI THEN 'CI' ELSE 'CS' END AS CheckRom

/*CI    CS  CI*/

- -:

CREATE TABLE #tempTable(deflt VARCHAR(100)
                       ,Arab VARCHAR(100) COLLATE Arabic_100_CS_AS_WS_SC
                       ,Rom VARCHAR(100) COLLATE Romanian_CI_AI);

INSERT INTO #tempTable(deflt,Arab,Rom) VALUES('aBc','aBc','aBc');

SELECT [name], system_type_name, collation_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM #tempTable',NULL,0);
DROP TABLE #tempTable;

/*
name    system_type_name    collation_name
deflt   varchar(100)        Latin1_General_CI_AS
Arab    varchar(100)        Arabic_100_CS_AS_WS_SC
Rom     varchar(100)        Romanian_CI_AI
*/

- . "" :

DECLARE @TableVariable TABLE(deflt VARCHAR(100)
                            ,Arab VARCHAR(100) COLLATE Arabic_100_CS_AS_WS_SC
                            ,Rom VARCHAR(100) COLLATE Romanian_CI_AI);

INSERT INTO @TableVariable(deflt,Arab,Rom) VALUES('aBc','aBc','aBc');

SELECT CASE WHEN tv.deflt = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckDefault
      ,CASE WHEN tv.Arab = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckArab
      ,CASE WHEN tv.Rom = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckRom
FROM @TableVariable AS tv

/*CI    CS  CI*/

. . ( NOT NULL, , , NULL).

, .

2:

, CS CI, :

DECLARE @tbl TABLE(SomeValueInDefaultCollation VARCHAR(100));
INSERT INTO  @tbl VALUES ('ABC'),('aBc');

DECLARE @CompareCaseSensitive BIT = 0;
DECLARE @SearchFor VARCHAR(100) = 'aBc';

SELECT *
FROM @tbl 
WHERE (@CompareCaseSensitive=1 AND SomeValueInDefaultCollation=@SearchFor COLLATE Latin1_General_CS_AS)
   OR (ISNULL(@CompareCaseSensitive,0)=0 AND SomeValueInDefaultCollation=@SearchFor COLLATE Latin1_General_CI_AS);

@CompareCaseSensitive, 1, aBc, NULL 0 .

- ! - , UDF.

+7

BINARY_CHECKSUM UDF :

SELECT txt
FROM (SELECT 'ABC' AS txt UNION SELECT 'cdf') t
WHERE 
BINARY_CHECKSUM(txt)= BINARY_CHECKSUM(UPPER(txt))
+6

, , . , , . .

.

SELECT txt
FROM (SELECT 'ABC' AS txt UNION SELECT 'cdf') t
WHERE txt collate SQL_Latin1_General_CP1_CS_AS = UPPER(txt)
+1

: , , 1 0.

CREATE FUNCTION [dbo].[fnsConvert]
(
      @p NVARCHAR(2000) ,
      @c NVARCHAR(2000)
)
RETURNS BIT
AS

    BEGIN
        DECLARE @result BIT

        IF ( @c = 'SQL_Latin1_General_CP1_CS_AS' )
        BEGIN
            IF @p COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(@p) 
                SET @result = 1
            ELSE
                SET @result = 0    
        END
        ELSE
            SET @result = 0 

        RETURN @result
    END

, , .

SELECT txt
FROM (SELECT 'ABC' AS txt UNION SELECT 'cdf') t
WHERE 
dbo.fnsConvert(txt, 'SQL_Latin1_General_CP1_CS_AS') = 1
+1

@Shnugo, ,

, , , :

select * from 
(SELECT 'ABC' AS txt UNION SELECT 'cdf') a
where (dbo.fnsConvert(txt, 'SQL_Latin1_General_CP1_CS_AS') 
collate SQL_Latin1_General_CP1_CS_AS)  = UPPER(txt)

collate , /, , , , ,

collation_name .

+1

@Shnugo, , .

TSQL () :

create table Q47890189 (
    txt nvarchar(100),
    colltxt as txt collate SQL_Latin1_General_CP1_CS_AS persisted
)

insert into Q47890189 (txt) values ('ABC')
insert into Q47890189 (txt) values ('cdf')

select * from Q47890189 where txt = UPPER(txt)
select * from Q47890189 where colltxt = UPPER(colltxt)

, , .

+1

COLLATE. , .

COLLATE . .

CREATE TABLE [dbo].[OINV]
    [CardCode] [nvarchar](50) NULL
)

5175460 rows , .

alter table OINV
alter column CardCode varchar(50)
--1 min 45 sec

alter table OINV
alter column CardCode nvarchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS

1 . , 5175460 . .

, , .

UDF/TVF ant . -

:

declare @i varchar(60)='ABC'

SELECT txt
FROM (SELECT 'abc' AS txt UNION SELECT 'cdf') t
WHERE 
txt  = @i COLLATE SQL_Latin1_General_CP1_CS_AS

,

declare @i varchar(60) COLLATE SQL_Latin1_General_CP1_CS_AS='ABC'

, , .

,

UDF ,

CREATE FUNCTION testfn (
    @test VARCHAR(100)
    ,@i INT
    )
RETURNS TABLE
AS
RETURN (
        -- insert into @t values(@test)
        SELECT @test COLLATE SQL_Latin1_General_CP1_CS_AS AS a
        )


SELECT *
FROM (
    SELECT 'ABC' AS txt

    UNION

    SELECT 'cdf'
    ) t
OUTER APPLY dbo.testfn(txt, 0) fn
WHERE fn.a = UPPER(txt)

, . TVF , .

TVF .

+1

MSDN COLLATE:

, , .

?

UDF, :

CREATE FUNCTION dbo.test 
(   
    @text nvarchar(max)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT c COLLATE SQL_Latin1_General_CP1_CS_AS as txt
    FROM (VALUES (@text)) as t(c)
)
GO

:

;WITH cte AS (
    SELECT N'ABC' as txt
    UNION 
    SELECT N'cdf'
)

SELECT c.txt
FROM cte c
OUTER APPLY dbo.test (c.txt) t
WHERE t.txt = UPPER(c.txt)

:

txt
------
ABC
+1

More articles:


All Articles