4 functions, 3 functions...2 & 1 is possible?

  • 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 ?

    karthik

  • 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/

  • --------------------------------------------------

    worldbaank

    countrybank

    districtbank

    internationalbank

    y

    expected output:

    sorry..as i was rush from the office to reach home, i didn't use " " to the string.

    But I don't want to hardcode the maximum length.

    karthik

  • Assuming that your fields are always as described you could use. However this probably won't perform as well as the 3 and 4 function methods.

    select parsename(replace(last_name,'_','.'),1)

    With the substring method you don't have to get the exact length of the remaining string, so you could do the following if you are really against hard coding a length

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

  • any other approach which will resolve this issue by using only one function or without using any functions?

    karthik

  • karthik M (12/10/2012)


    any other approach which will resolve this issue by using only one function or without using any functions?

    CLR. If you mean native, then no.


    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/

  • karthik M (12/10/2012)


    any other approach which will resolve this issue by using only one function or without using any functions?

    If you don't count the functions inside the function, this one:

    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);

    go

    select

    *

    from

    #testEnvironment

    cross apply dbo.DelimitedSplit8K(last_name,'_')

    where

    ItemNumber = 2;

    go

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

    BEGIN

    DROP TABLE #testEnvironment;

    END;

    You will find the code for the dbo.DelimitedSplit8K function here: http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply