Position within String (INSTR or LOCATE() ?)

• Neuro

  Ten Centuries

  Points: 1063

  More actions

  November 28, 2005 at 4:28 am

  #90034

  I have a field which has postcodes

  What I would like to be able to do is find the Alphabetic part of the postcode

  my idea was to look at the first four characters of the field and then find the position of the first number

  eg

  Data In : TW11 7PR

  SELECT LEFT('TW11 7PR',4)

  TW11

  But now I need to fidn the position of the first numberic and return only TW

  A= Represents A-Z

  #= REpresents 0-9

  The data is in these formats

  AA## #AA

  A# #AA

  AA#A #AA

  A## #AA

  A# ##A

  your help would be appreciated guys, Im using Query Analyzer 2000

   

   

   

   

   

   

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 28, 2005 at 5:05 am

  #606040

  Something like this?

  DECLARE @a VARCHAR(20)

  SET @a = 'TW11 7PR'

  SELECT LEFT(@a,PATINDEX('%[0-9]%',@a)-1)

  SET @a = 'TESTME_All OVER11 7PR'

  SELECT LEFT(@a,PATINDEX('%[0-9]%',@a)-1)

                      

  --------------------

  TW

  (1 row(s) affected)

                      

  --------------------

  TESTME_All OVER

  (1 row(s) affected)

   

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Neuro

  Ten Centuries

  Points: 1063

  More actions

  November 28, 2005 at 5:09 am

  #606042

  LOL Thanks Frank

  I just got my answer before I checked your post, what I did was

   

  SELECT case

  when patindex('%[0-9]%',ADR.ZIPCODE) > 0

  then left(ADR.ZIPCODE,patindex('%[0-9]%',ADR.ZIPCODE)-1)

  else ADR.ZIPCODE

  end          as prefix

   

  I was really looking forward to answering my own post

  Thanks for your help though