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 |
|
Birgit
Starting Member
5 Posts |
 Posted - 2011-02-09 : 04:40:39
|
| I need to use the roadnumber from a textstring in a update query.My text is in a column called "gade" (like streetname) and it consists of something like this: "Voldgade 16 B,1 tv".From this textstring I have to use "16 B" and change it to "016B".I have to put a 0 in the front and delete the space. Everything from the , is to be deleted.I hope someone can help me - thanksBirgit |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-09 : 05:02:48
|
| something like this?declare @var varchar(100)set @var='Voldgade 16 B,1 tv'Select left(REPLACE (@var,'16 B','016B'),charindex(',',REPLACE (@var,'16 B','016B'))-1) |
 |
|
|
Birgit
Starting Member
5 Posts |
Posted - 2011-02-09 : 05:55:49
|
| I'm sorry but it doesn't solve my problem:-(I have a list with 5 mill records where maybe a 4th of them includes a "," and also a 4th includes a character after the number. In all situations I have to combine the number with 0's in front of (so there are 3 characters - ex. 005 or 016) and then eventually a character right after the number (like ex. 005A or 016B).In addition the first text string might contain spaces as part of the name like "Example Street", that should be left untouched. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-09 : 06:25:26
|
| This is 100% guaranteed to be a MAJOR main in the a** unless there is a consistent way of writing your street addresses, which in 99% of the systems I know and have heard of isn't. What if the street address is "Kong Haakon den 5.sgade 72" or "St.Olavsgade 15 4 etage"? With 5 million addresses I'm pretty sure you have quite a few variations...- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
Birgit
Starting Member
5 Posts |
Posted - 2011-02-09 : 08:35:09
|
| Yes your are very right. There are so many different ways in writing adresses, so I need a script that does the following:- finds the first number i the text - measures the lenght of the number - from space to space- puts zeros in from of the number so there are 3 characters (ex 005)- finds a character (A, B, C ...) right after the number - puts the character rihgt after the 3 characters with number without a space(ex 005B)That simple - I just cannot write the script :)Birgit |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-09 : 08:48:34
|
Better get working on 1 at a time. No matter what script any of us attempt to provide, nothing will help you more than cracking into writing the code yourself. Not to mention, there is no amount of code that will do them "all" correctly.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Birgit
Starting Member
5 Posts |
Posted - 2011-02-09 : 09:25:00
|
| I'm aware of that - I need to know how to find the numbers - do I find them with [0-9] or something like that - and how do I place my result in a "placeholder" until I can use it further? |
|
|
|
|
|
|
|
|
update query