returning only a portion of a textbox field

  • Hello

     

    I need to return only in my select the following value of the content of my field name CODPOST:

    ex: 4520-169 FEIRA

    i need to return only FEIRA

    I can do that ,  i can use right(substring(codpost,1,char(13)), the ideal solution is to search for a determinate character to divide the string in this CHR(13).

    Anyone could help me

    Thanks and Merry Christmas for all the comunity

    Luis Santos

     

     

  • Have you looked into CHARINDEX and PATINDEX in BOL?  I think that's what you are looking for.

     

  • Your first solution looks fine to me, but this will find the character position of the (first occurrence of) space

    declare @STR varchar(50), @pos int

    set @STR = '4520-169 FEIRA'

    set @pos = charindex(' ', @STR)

    select @STR String, @pos SpacePosition

    I am also intrigued as to the data held in a field called 'CODPOST' - sounds a bit fishy to me

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I'm not sure I completely understand your question, but see if this example helps:

    DROP TABLE codPostTest

    GO

    CREATE TABLE codPostTest

    (

      id int IDENTITY(1,1)

    , codpost varchar(20)

    )

    GO

    SET NOCOUNT ON

    INSERT codPostTest (codpost) VALUES ('4520-169 FEIRA')

    INSERT codPostTest (codpost) VALUES ('4520-169 ANDY')

    INSERT codPostTest (codpost) VALUES ('4520-169 BRIAN')

    INSERT codPostTest (codpost) VALUES ('4520-169 STEVE')

    INSERT codPostTest (codpost) VALUES ('4520-169')

    INSERT codPostTest (codpost) VALUES ('4520-169 ')

    SET NOCOUNT OFF

    SELECT codpost

         , CASE

             WHEN CharIndex(' ', RTrim(codpost)) = 0 THEN codpost -- or possibly '' or NULL

             ELSE Substring(codpost, CharIndex(' ', codpost) + 1, Len(codpost))

           END AS codpostName

      FROM codPostTest

Viewing 4 posts - 1 through 3 (of 3 total)

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