abksharma abksharma - 8 days ago 6
MySQL Question

replicating CHARINDEX with a new user defined function in mysql

Can some one replicate CHARINDEX with new user defined function. (using instr)

IF(CHARINDEX('(G)',v_username) = 0) THEN


mysql version. If could keep the function same my migration will be become more smooth.

IF(INSTR(v_username,'(G)') = 0) THEN

Answer

Yes, this can be done. One approach is to create a user-defined function that wraps CharIndex.

-- CHARINDEX wrapper.
CREATE FUNCTION INSTR
    (
        @Source     NVARCHAR(255),      -- Search this..
        @Find       NVARCHAR(255)       -- ...for this.
    )
RETURNS NVARCHAR(255)
AS
BEGIN   
    RETURN CHARINDEX(@Find, @Source)
END

The downside to this approach is UDFs cannot compete, in terms of performance, with native functions.

You might be able to reduce the impact by looking at the common language runtime. The CLR allows you to write functions, SPs, etc that compile to native code.

All things considered; I would recommend you update your code instead. Although they can be long and painful, migrations are a one-off task.