Select the first string

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ddtpmyra
    Contributor
    • Jun 2008
    • 333

    Select the first string

    I wanted to select the first word before spaces.

    ex.
    Science 101


    result:
    Science
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    You can find the first space using CHARINDEX() and then use LEFT() to get those first characters.

    Comment

    • ddtpmyra
      Contributor
      • Jun 2008
      • 333

      #3
      can you give me the whole sample syntax using both?

      Comment

      • ddtpmyra
        Contributor
        • Jun 2008
        • 333

        #4
        I use the sytax below but it didn't pick subject names with one work only.

        ex.
        science 101
        history
        science 102
        biology
        physical education

        result:
        science
        science
        physical

        what I wanted is to select all but take out the 2nd word after a space

        result wanted:
        science
        history
        science
        biology
        physical


        Code:
        SELECT subject, ltrim(Left(subject, CHARINDEX(' ', subject)))FROM dapdata

        Comment

        • Rabbit
          Recognized Expert MVP
          • Jan 2007
          • 12517

          #5
          Append a space to the end of subject before using charindex.

          Comment

          • ddtpmyra
            Contributor
            • Jun 2008
            • 333

            #6
            sorry rabbit i cannot understand what to do, can you help by re-writing my sql statement? i would appreciate it! :)

            Comment

            • Rabbit
              Recognized Expert MVP
              • Jan 2007
              • 12517

              #7
              Append/concatenate a space.
              subject + ' '

              Comment

              • ddtpmyra
                Contributor
                • Jun 2008
                • 333

                #8
                it works! thanks Rabbit!
                Code:
                SELECT subject, ltrim(Left(subject, CHARINDEX(' ',subject + ' ' )))FROM dapdata

                Comment

                Working...