returning only a portion of a textbox field

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  December 21, 2005 at 4:16 am

  #111120

  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

   

   

• Tyson L. Price

  Mr or Mrs. 500

  Points: 599

  More actions

  December 21, 2005 at 5:28 am

  #610793

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

   

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  December 21, 2005 at 5:43 am

  #610799

  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

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• vadba

  SSChampion

  Points: 11133

  More actions

  December 22, 2005 at 6:31 am

  #611094

  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