Announcement

Collapse
No announcement yet.

Query to return all text up until a perticular characher

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query to return all text up until a perticular characher

    Hi guys,

    Im trying to create a query which returns all text up until the first '\' so for example here is my current query

    Code:
    SELECTpath,SUBSTRING(Path,17,45)as Data
    FROM Table
    WHEREPathlike'\Enq%'AND
    Deleted ='0'
    Current output is

    ENQ-000017-Testing\01. Test Folder\Data 1\Data 2
    ENQ-000018-Testing\01. Test Folder\Data 1\Test
    ENQ-000018-Testing\01. Test Folder\Data 1\Test\Data 3
    ENQ-000019-Testing\01. Test Folder
    ENQ-000019-Testing\01. Test Folder
    ENQ-000019-Testing\01. Test Folder\Test Folder 2
    ENQ-000020-Testing\01. Test Folder\Test Folder 3

    But I only want the text up until the first backslash so results should be

    ENQ-000017-Testing
    ENQ-000018-Testing
    ENQ-000019-Testing
    ENQ-000020-Testing

    Any thoughts how to achieve this?

    Thanks as always

  • #2
    Use CHARINDEX (SQL 2008 or higher):
    https://docs.microsoft.com/en-us/sql...x-transact-sql
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Thanks I looked at charindex but it only returns the first letter\number rather than everything up until the next backslash

      Code:
      SELECTpath,substring(path,17,CHARINDEX('\',path))As Data
      FROM Table
      WHEREPathlike'\ENQ%'AND
      Deleted ='0'
      So results are
      Path Data
      \Enquiries\2014\Test Data 123\Testing ABC T
      \Enquiries\2014\New Folder\Another Folder N
      \Enquiries\2014\ABC123\123 567\Test A
      But I would like
      Path Data
      \Enquiries\2014\Test Data 123\Testing ABC Test Data 123
      \Enquiries\2014\New Folder\Another Folder New Folder
      \Enquiries\2014\ABC123\123 567\Test ABC123

      Comment


      • #4
        I managed it with SELECT LEFT(path, CHARINDEX('\', path)) AS LeftPath - adapt as appropriate
        Tom Jones
        MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
        PhD, MSc, FIAP, MIITT
        IT Trainer / Consultant
        Ossian Ltd
        Scotland

        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment


        • #5
          Thanks will give it a bash

          Comment


          • #6
            Hi,
            I tried it but no worky - if I use the SELECT LEFT without the substring it gives me the initial \ which the substring filters out. Is there anyway to select left after a substring?

            Comment


            • #7
              Can you post the exact SQL query you are using (obfusticate table names if needed)?

              Confused by "select left after a substring" - I thought from original post you wanted left string up to first occurrence of '\' ??? Mind you, second post contradicts that
              Tom Jones
              MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
              PhD, MSc, FIAP, MIITT
              IT Trainer / Consultant
              Ossian Ltd
              Scotland

              ** Remember to give credit where credit is due and leave reputation points where appropriate **

              Comment


              • #8
                Sure so if I do a bog standard select

                Code:
                SELECTpath
                FROM [DB].[dbo].[Projects]
                WHEREPathlike'\ENQ%\'AND
                Deleted ='0'
                The results are
                \Enquiries\2014\
                \Enquiries\2014\Test 1\Folder 1\
                \Enquiries\2016\Folder 2\Folder 3\Folder 4\
                \Enquiries\2017\Folder 3\Folder 4\Folder 5\
                \Enquiries\2016\Folder 35\Folder 54\Folder 55\
                So I modify that with a substring to remove the first 16 characters and starts on the 17th

                Code:
                SELECTsubstring(path,17,CHARINDEX('\',Path)+LEN(Path))As Data
                FROM [EDB].[dbo].[Projects]
                WHEREPathlike'\ENQ%\'AND
                Deleted ='0'
                Resulting in
                Test 1\Folder 1\
                Folder 2\Folder 3\Folder 4\
                Folder 3\Folder 4\Folder 5\
                Folder 35\Folder 54\Folder 55\
                But what Im actually after is
                Test 1
                Folder 2
                Folder 3
                Folder 35
                Hope this clears up any confusion?

                Thanks

                Comment


                • #9
                  Ah - my query (from your OP) was giving you the left part, so just the "enquiries 2013" etc.

                  Let me look and think

                  Is it always "\Enquires\yyyy" ?
                  Last edited by Ossian; 10th May 2017, 11:17.
                  Tom Jones
                  MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
                  PhD, MSc, FIAP, MIITT
                  IT Trainer / Consultant
                  Ossian Ltd
                  Scotland

                  ** Remember to give credit where credit is due and leave reputation points where appropriate **

                  Comment


                  • #10
                    Originally posted by Ossian View Post
                    Ah - my query (from your OP) was giving you the left part, so just the "enquiries 2013" etc.

                    Let me look and think

                    Is it always "\Enquires\yyyy" ?
                    Cool thanks - and yes its always that format \Enquiries\YYYY\XXXXXXX\.....

                    Comment


                    • #11
                      OK, on the assumption that your data always is in the form '\Enquiries\yyyy\wanted data\unwanted data
                      You will need to create a custom UDF

                      CREATEFUNCTION dbo.GetTextBetweenSlashes(@text VARCHAR(100))

                      RETURNSVARCHAR(100)

                      AS

                      BEGIN

                      DECLARE @start INT

                      DECLARE @end INT

                      DECLARE @result VARCHAR(100)

                      SET @result ='Not Found'

                      SELECT @start =CHARINDEX('\', @text,15) --based on assumption about data format

                      SELECT @end =CHARINDEX('\', @text,CHARINDEX('\', @text,15)+1)

                      IF @start <> 0 AND @end <> 0 -- deals with inappropriate data

                      BEGIN

                      SELECT @result =SUBSTRING(@text, @start+1, @end -@start-1)

                      END

                      RETURN @result

                      END


                      You can use it in your Select statement:
                      SELECT dbo.GetTextBetweenSlashes(Path)
                      Tom Jones
                      MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
                      PhD, MSc, FIAP, MIITT
                      IT Trainer / Consultant
                      Ossian Ltd
                      Scotland

                      ** Remember to give credit where credit is due and leave reputation points where appropriate **

                      Comment


                      • #12
                        Thanks, Im not too ofay with SQL (not a newbie but not as experienced as yourself) is a function like a stored procedure or do you run it each time you query? I have read up on docs.micrsoft.com but the example and explanation is clear as mud and I haven't got any headphones for a youtube audio\video explanation

                        Also given this DB is managed by a 3rd party application are there any issues creating a UDF? I know some support companies don't like users messing with the DB (backend)
                        Last edited by 5habbaranks; 10th May 2017, 12:28.

                        Comment


                        • #13
                          A function is like a stored proc except it cannot modify data - takes parameters, returns results etc.

                          You will need permission to create the function in your database - IMHO give the support company the code and ask them to do it, alternatively create a new empty database and create the function there, then use the 3 part naming convention to access it (SELECT dbname.dbo.fnname(params)
                          Tom Jones
                          MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
                          PhD, MSc, FIAP, MIITT
                          IT Trainer / Consultant
                          Ossian Ltd
                          Scotland

                          ** Remember to give credit where credit is due and leave reputation points where appropriate **

                          Comment


                          • #14
                            Thanks Ive asked if they have any issues with me adding additional functions will see what comes back. Ive never added\written functions before but I know from previous experience some companies get funny when you start delving into the back end (even though its just querying rather than modifying\writing).

                            Thanks again

                            Comment

                            Working...
                            X