Professional Web Applications Themes

query help with substring - Microsoft SQL / MS SQL Server

Hi With your example data the queries could be SELECT LEFT(Name,6), COUNT(*) FROM #test GROUP BY LEFT(Name,6) ORDER BY LEFT(Name,6) and SELECT LEFT(Name,6), RIGHT(Name,DATALENGH(Name)-6) FROM #test If your data is more complex than the example you will probably have to use either PATINDEX to get the first non-alpha character or a combination of CHARINDEX, REVERSE to get the last space. John "Hassan" <com> wrote in message news:phx.gbl... ...

  1. #1

    Default Re: query help with substring

    Hi

    With your example data the queries could be

    SELECT LEFT(Name,6), COUNT(*)
    FROM #test
    GROUP BY LEFT(Name,6)
    ORDER BY LEFT(Name,6)

    and

    SELECT LEFT(Name,6), RIGHT(Name,DATALENGH(Name)-6)
    FROM #test

    If your data is more complex than the example you will probably have to use
    either PATINDEX to get the first non-alpha character or a combination of
    CHARINDEX, REVERSE to get the last space.

    John

    "Hassan" <com> wrote in message
    news:phx.gbl... 


    John Guest

  2. #2

    Default Re: query help with substring

    Actually more generic so I can work with the profiler data that i save in
    the table ..There are a bunch of stored procedures being captured and I want
    to get the avg(duration) , avg(CPU),etc... and the stored procedure names
    could be of varying length such as "exec proc1 1,2" or exec "procedure1,1"
    or "exec storedprocedure 1,2"

    Can you help in such a situation with the query ? Basically heres a new set
    of inserts to work with

    Insert #test values('exec sproc 1 ',1)
    Insert #test values('exec sproc 2 ',1)
    Insert #test values('exec sproc 3',1)
    Insert #test values('exec sproc 4',1)
    Insert #test values('exec storedproc 1',1)
    Insert #test values('exec storedproc' ,1)
    Insert #test values('exec dbo.storedprocedure 1 ',1)


    "John Bell" <com> wrote in message
    news:3f486724$0$10770$easynet.co.uk... 
    use [/ref]
    column 
    >
    >[/ref]


    Hassan Guest

  3. #3

    Default Re: query help with substring

    Hassan (com) writes: 

    Note that your example had varchar(20), and the last row exceeds that.

    Here is a query:

    select substring(Name, 1, charindex(' ', Name))
    from (select Name = replace(Name, 'exec ', '') + ' ' FROM #test) as t

    In the derived table I strip out EXEC, as I figure it's redudant anyway.
    I also add an extra space at the end which is good for the outer
    expression where I get all data up to the first space. (Had I not
    added the extra space, I would have need to special-case handle the
    case that there is no space in the string.)

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  4. #4

    Default Re: query help with substring

    Actually I wanted a group by too so that results are

    Name Count
    exec sproc 4
    exec storedproc 2
    exec dbo.storedprocedure 1


    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... 
    >
    > Note that your example had varchar(20), and the last row exceeds that.
    >
    > Here is a query:
    >
    > select substring(Name, 1, charindex(' ', Name))
    > from (select Name = replace(Name, 'exec ', '') + ' ' FROM #test) as t
    >
    > In the derived table I strip out EXEC, as I figure it's redudant anyway.
    > I also add an extra space at the end which is good for the outer
    > expression where I get all data up to the first space. (Had I not
    > added the extra space, I would have need to special-case handle the
    > case that there is no space in the string.)
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Hassan Guest

  5. #5

    Default Re: query help with substring

    Hassan (com) writes: 

    I assumed that the difficult part was to extract the procedure name.
    I figured that whatever you wanted to do with the data, you could work
    out on your own. Anyway, let's use one more derived table:

    SELECT Name, COUNT(*)
    FROM (select Name = substring(Name, 1, charindex(' ', Name))
    from (select Name = replace(Name, 'exec ', '') + ' '
    FROM #test) as t) as x
    GROUP BY x



    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. sql substring and cf mid functions
    By trojnfn in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: January 30th, 09:09 PM
  2. Query Of Query Substring Issue
    By Gdev in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: November 6th, 01:12 AM
  3. Using a substring in SQL WHERE
    By ghouser in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 31st, 11:33 AM
  4. AREs in substring(from)
    By Brandon Craig Rhodes in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 22nd, 04:50 PM
  5. substring: to the end of the string
    By KONTRA Gergely in forum Ruby
    Replies: 18
    Last Post: November 18th, 11:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139