Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Reedar
Starting Member
6 Posts |
 Posted - 2008-01-02 : 08:24:12
|
| Hi Guys, Wonder if someone can help me out? I have a database which contains names and addresses. I need to populate the District and Sector fields based on the contents on the Postcode field.For Example; if Jon Smith lives at 1 High Street and his Postcode is 'BA11 2TR' and need to put 'BA11' in the District column, and 'BA11 2' into the sector.Is anyone able to point me in the right direction for the syntax here? I know how to select info based on a certain number of chars from the left/right, but dealing with the space is throwing me out. Many thanks in advance. Reedar |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-01-02 : 08:25:42
|
you can use charindex to find the position of the space in the string and then use left() to extract whatever before the space char.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-01-02 : 08:28:21
|
quote:
and 'BA11 2' into the sector
what is the rule for this ? 1 char after the space ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Reedar
Starting Member
6 Posts |
Posted - 2008-01-02 : 08:34:08
|
quote:
what is the rule for this ? 1 char after the space ?
Exactley. I'm having to base the select on chars until the space as opposed to just a certain number of chars as the number of chars before a space varies between 2-4.Thanks for the speedy respone btw.Reedar |
|
|
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-01-02 : 17:40:52
|
| Im assuming you found your answer... for those who might come across this later :declare @string varchar(10)select @string = 'BA11 2TR'select substring(@string, 1, charindex(' ', @string)-1) as District, substring(@string, 1, charindex(' ', @string)+1) as Sector |
|
|
|
Reedar
Starting Member
6 Posts |
Posted - 2008-01-17 : 17:13:38
|
| Hi Guys,Yes I did, and many thanks for all your help!!!Reedar |
|
|
|
|
|
|
|
|
Help a junior dba....!