Adding function in t-sql to make things simpler

Question

insert into @res
    select * from (
        select * from myTable where my_name like '%'+@token+'%'
    union

        --replace special characters
        select * from myTable where my_name like '%'+REPLACE ( @token , 'e' , 'è' )+'%' order by name
        select * from myTable where my_name like '%'+REPLACE ( @token , 'e' , 'é' )+'%' order by name
        select * from myTable where my_name like '%'+REPLACE ( @token , 'e' , 'Ě' )+'%' order by name
        ----

) as tmp order by name

The part of the code which is between the comments is much longer than here.

How to move it to a new function, without losing its functionality?

bugartist · Accepted Answer · 2013-04-01 10:46:38Z

You could use LIKE with string like this %ar[e,è,é,Ě]na% which will cover all your unions. Still if the rules are too many, here is a function that will do this:

Here you go

CREATE FUNCTION transform (@inStr varchar(300))
returns varchar(255)
AS  
BEGIN
DECLARE @registry varchar(300),
    @curChar varchar(20),
    @outStr varchar(300),
    @counter int,
    @start int,
    @end int;

SET @outStr = '%';
/* Creating a registry of replacements in format {X}[x,X,Xx];
   Where {X} contains the character to be replaced,
   [x,X,Xx]; contains the replacemet characters
*/
SET @registry = '{e}[e,è,é,Ě];
                 {s}[ ..other translations of "s" go here.. ];
                 {n}[n,N];';

set @counter = 1;

WHILE (LEN(@inStr) >= @counter)
    BEGIN
        SELECT @curChar = substring(@inStr, @counter, 1)
        IF (CHARINDEX( '{' + @curChar + '}', @registry, 1) > 0)
            BEGIN
                SELECT @start = CHARINDEX( '{' + @curChar + '}', @registry, 1) + 2;
                SELECT @end = CHARINDEX( ';', @registry, @start);
                SELECT @curChar = substring(@registry, @start + 1, @end - @start - 1);
            END

        SET @outStr = @outStr + @curChar
        SET @counter = @counter + 1;
    END 
SET @outStr = @outStr + '%'

RETURN @outStr; 
END

for example here

... WHERE x.str like transform('arena')

the function will return %ar[e,è,é,Ě][n,N]a%. This string means - any value that contains a string starting with ar, next char is any of e,è,é,Ě, next char any of n,N and ends with a.

So...

    select * from myTable where my_name like transform(@token)

Will cover any variation and you no longer will need these unions.

Collectives™ on Stack Overflow

Adding function in t-sql to make things simpler

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related