abksharma abksharma - 1 year ago 98
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 Source

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

-- CHARINDEX wrapper.
        @Source     NVARCHAR(255),      -- Search this..
        @Find       NVARCHAR(255)       -- ...for this.
    RETURN CHARINDEX(@Find, @Source)

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download