CODEWITHSUNDEEP
sql-servert-sql
JaimeCamargo
JaimeCamargo

Reputation: 363

Get column collation without flags CI, CS, AI, AS

Is it possible get the column collation string without flags CI, CS, AI, AS?

For example:

If we have "Latin1_General_100_CS_AS" I need "Latin1_General_100" without "_CS_AS".

Thanks.

Upvotes: 1

Views: 2113

Answers (3)

zajonc
zajonc

Reputation: 1969

Here you are a customable solution to exlude any falg you want from a table of names (for example collation names). If you want you can prepare a procedure or function to do it easier in your batches.

-- declaration of the input table
declare @input as table (name varchar(max));

-- declaration of the output table
declare @output as table (name varchar(max));

-- declaration of the table with flags you want to exclude from names
declare @exclude as table (flag varchar(max));

-- put flags you want to exclude into array
insert into @exclude (flag)
values ('CI'), ('CS'), ('AI'), ('AS');

-- put some collations into input table (as example for testing)
insert into @input
select distinct name
from sys.fn_helpcollations();

-- now we need to loop over all value in exclude table
declare @excludeCursor as cursor;
declare @flag as varchar(max);

set @excludeCursor = cursor for
select flag
from @exclude;

open @excludeCursor;

fetch next from @excludeCursor INTO @flag;
while @@FETCH_STATUS = 0
begin
    update @input
    set name = replace(name, concat('_', @flag), '')
    where name like concat('%_', @flag)
    or name like concat('%_', @flag, '_%')

    -- The where clause above ensure that you replace exactly the flag added to your exclude table.
    -- Without this caluse if you want to excldue for example a BIN flag and you have a BIN and BIN2 flags in your names
    -- then the '2' character from the BIN2 will stay as a part of your collation name.

    fetch next from @excludeCursor INTO @flag;
end

close @excludeCursor;
deallocate @excludeCursor;

-- prepare the output table with distinct values
insert into @output
select distinct name
from @input;

-- print the output
select *
from @output

Upvotes: 0

bitch_cakes
bitch_cakes

Reputation: 126

You can use the SQL replace function to remove instances of '_CI' in your query.

For example, the code below will remove _CI and _AI from collation names. You can use similar logic to remove the other characters you don't want. 

SELECT name, REPLACE(REPLACE(name, '_CI',''), '_AI','') description FROM sys.fn_helpcollations();

Upvotes: 0

Mike
Mike

Reputation: 1667

CS = Case Sensitive CI= Case Insensitive you need to have one or the other.

AS = Accent sensitive, AI = Accent Insensitive.

These codes specify how to sort. You need to select CI or CS and AS or AI

https://msdn.microsoft.com/en-us/library/ms143726.aspx

Upvotes: 1

Related Questions