• karthik M (12/10/2012)


    create table emp

    (

    last_name varchar(50)

    )

    insert into emp

    select abc_worldbaank

    union

    select xyzabc_countrybank

    union

    select yyybb_districtbank

    union

    select zzzaaa_internationalbank

    my requirement is to display the text after '_'

    worldbaank

    countrybank

    districkbank

    internationalbank

    I used two method

    1) select substring(last_name, charindex('_',last_name), len(last_name) - charindex('_',last_name))

    -- 4 functions

    2) select right(last_name, len(last_name) - charindex('_',last_name))

    -- 3 fucntions

    is it possible to do this by using 2 or only one string function ?

    or else is it possible to do without using any string funtion ?

    Your two queries do no produce the same result.

    Number 1 produces: -

    --------------------------------------------------

    _worldbaan

    _countryban

    _districtban

    _internationalban

    Number 2 produces: -

    --------------------------------------------------

    worldbaank

    countrybank

    districtbank

    internationalbank

    Which did you want?

    Your create script doesn't work, here is a fixed version: -

    IF object_id('tempdb..#testEnvironment') IS NOT NULL

    BEGIN

    DROP TABLE #testEnvironment;

    END;

    CREATE TABLE #testEnvironment (last_name VARCHAR(50));

    INSERT INTO #testEnvironment

    SELECT last_name

    FROM (VALUES ('abc_worldbaank'),

    ('xyzabc_countrybank'),

    ('yyybb_districtbank'),

    ('zzzaaa_internationalbank')

    ) a(last_name);

    You could also have written it like this, if you want it to be accessable for those not running SQL Server 2008 or SQL Server 2012: -

    IF object_id('tempdb..#testEnvironment') IS NOT NULL

    BEGIN

    DROP TABLE #testEnvironment;

    END;

    CREATE TABLE #testEnvironment (last_name VARCHAR(50));

    INSERT INTO #testEnvironment

    SELECT 'abc_worldbaank'

    UNION ALL SELECT 'xyzabc_countrybank'

    UNION ALL SELECT 'yyybb_districtbank'

    UNION ALL SELECT 'zzzaaa_internationalbank';

    If you want to use only two functions, you'll have to rely on the fact that you know the data-type is VARCHAR(50).

    So, since you know the size is 50 at maximum, you can do this: -

    SELECT SUBSTRING(last_name, CHARINDEX('_', last_name) + 1, 50)

    FROM #testEnvironment;

    Which produces: -

    --------------------------------------------------

    worldbaank

    countrybank

    districtbank

    internationalbank

    Or if the other format was correct, you can do this: -

    SELECT SUBSTRING(last_name, CHARINDEX('_', last_name), 50)

    FROM #testEnvironment;

    Which produces this: -

    --------------------------------------------------

    _worldbaank

    _countrybank

    _districtbank

    _internationalbank


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/