returning all but 2 rightmost characters 1

[pmsbony]

I am trying to run a select query on a table containg UK postcode data. The field entries are varying lenght and divided into two section example entries would be:

N1 9PF
Wc1 1HF
DA1 1DZ
WC1a 1fg

in the examples above I want all the characters before the space and the first one after the space. The position of the space is not constant (i suppose I could delete the space and add it in afterwards? but this seems an inelegant solution)

Basically what I want to do is remove the last two characters from the entries. using the select left(columnname, characters) is not feasible in this case

any suggestions would be gratefully recieved.

peter

 

[Spellman]

Try using the CHARINDEX function in conjunction with the left function so

Select left(fieldname, CharIndex(" ", fieldname)+1) from tablename.

Hope this helps.

Mark

 

[TheInsider]

wouldn't this be:

select left(columnname, len(columnname) - 2)

 

[pmsbony]

Would like to say ta muchly for the advice, you have saved me a fair bit of time

pete