SQL Server Performance

Please give me optimized query

Discussion in 'SQL Server 2008 General Developer Questions' started by kdineshbabuct, Jul 29, 2009.

  1. kdineshbabuct New Member

    This is my expectation
    Table
    id Value
    1 abd<sc>hhhh<kk>hhh<lk>
    2 jjj<kk>hhh<iii>dh<lll>
    3 kkk<kk>llll<hjg>fff
    This is my ex table. I need to print tagged information.
    Expected result
    <sc>
    <KK>
    <lk>
    <kk>
    <iii>
    I am very new to SQL Server . I plan to do this task using cursor . is it any otherway to do this. if i do it in single query then that would be better.
    please send working code snippets.Thanks in advance
    regards
    Dinesh Babu.K
  2. FrankKalis Moderator

    [quote user="kdineshbabuct"]
    This is my expectation
    Table
    id Value
    1 abd<sc>hhhh<kk>hhh<lk>
    2 jjj<kk>hhh<iii>dh<lll>
    3 kkk<kk>llll<hjg>fff
    This is my ex table. I need to print tagged information.
    Expected result
    <sc>
    <KK>
    <lk>
    <kk>
    <iii>
    I am very new to SQL Server . I plan to do this task using cursor . is it any otherway to do this. if i do it in single query then that would be better.
    please send working code snippets.Thanks in advance
    [/quote]
    Welcome to the forum!
    There is no such thing as a free lunch. When you don't provide working table definitions and sample data, you can't expect us to provide a working code snippet. [;)]
    A function or a cursor surely will do the trick, but to be honest I wouldn't this kind of thing in SQL Server.
  3. Madhivanan Moderator

    Where do you want to show data?
  4. kdineshbabuct New Member

    I need to print data in screen itself [:(]
  5. Madhivanan Moderator

    <P mce_keep="true">[quote user="kdineshbabuct"] <P mce_keep="true">&nbsp;</P><P><FONT face="Times New Roman" size=3>I need to print data in screen itself <IMG alt=Sad src="http://sql-server-performance.com/Community/emoticons/emotion-6.gif"></FONT>[/quote]</P><P>I mean "Do you want to show in front end application?"</P>
  6. kdineshbabuct New Member

    Thanks for ur reply. I am searching for a receipe not a free lunch[:D]
  7. Madhivanan Moderator

    declare
    @s varchar(100)declare
    @temp table(id int identity(1,1),val varchar(100))set
    @s='abd<sc>hhhh<kk>hhh<lk>'select
    @s=replace(replace(@s,'<','.'),'>','.')while
    charindex('.',@s)>0begin
    insert
    into @temp(val)select
    substring(@s,1,charindex('.',@s)-1)set
    @s=substring(@s,charindex('.',@s)+1,len(@s))endselect
    val from @tempwhere id%2=0
  8. FrankKalis Moderator

    Okay, one way could be something like this:
    IF OBJECT_ID ('dbo.SplitMe') IS NOT NULL
    DROP FUNCTION dbo.SplitMe;
    GO
    CREATE FUNCTION dbo.SplitMe(@Input varchar(1000))
    RETURNS @t TABLE (c1 varchar(100))
    AS
    BEGIN
    DECLARE @pos int;

    SET @pos = CHARINDEX('<', @Input, 0);

    WHILE @pos > 0
    BEGIN
    INSERT INTO @t
    SELECT SUBSTRING(@Input, @pos, CHARINDEX('>', @Input, @pos) - @pos + 1);
    SET @pos = CHARINDEX('<', @Input, @pos + 1);
    END
    RETURN;
    END
    GO

    DECLARE @tb TABLE (id int, c1 varchar(1000));

    INSERT INTO @tb
    SELECT 1, 'abd<sc>hhhh<kk>hhh<lk>'
    UNION ALL
    SELECT 2, 'jjj<kk>hhh<iii>dh<lll>'
    UNION ALL
    SELECT 3, 'kkk<kk>llll<hjg>fff'


    SELECT
    X.c1
    FROM
    @tb T1
    CROSS APPLY
    (SELECT
    *
    FROM
    dbo.SplitMe(T1.c1)) X
    <sc>
    <kk>
    <lk>
    <kk>
    <iii>
    <lll>
    <kk>
    <hjg>

    (8 row(s) affected)

  9. kdineshbabuct New Member

    Thanks a lot.it's working fine
  10. atulmar New Member

    Use built-in functions in SQL Server,
    SUBSTRING, CHARINDEX, LEFT, RIGHT
    You will get it yourself.

Share This Page