February 12, 2011 at 3:44 am
Hi,
Please help me on the following,
create table address(postcode nvarchar(100),Country nvarchar(100),clean_postcode nvarchar(100))
insert into address(postcode,country) values('3456','Denmark')
insert into address(postcode,country) values('DK 3215',Denmark')
insert into address(postcode,country) values('Copenhagen 3276','Denmark')
create table lookup(country nvarchar(100), psttern nvarchar(100))
insert into lookup(country,pattern) values ('Denmark','9999')
insert into lookup(country,pattern) values ('Denmark','XX 9999')
My goal is to update the column clean_postcode in address table with the values '3456','DK 3215','3276) by joining lookup on country.
But the update join statement (using the patindex string function ) is populating the clean_postcode column
with the values '3456','3215','3276' even though there exist a pattern like xx 9999.
Is there any way to tell the control to take the max len pattern that matches with postcode on address.country = lookup.country ?
any help is really appreciated
February 12, 2011 at 9:36 am
Whooo.. i really don't understand the requirement.. if u would be precise in what u want, i think i can help you out..
Can u give us what ur original UPDATE statement is like, what your expected output is ??
February 12, 2011 at 11:29 am
Thank you for the reply.
My query is
update address
set clean_postcode = substring(postcode,
patindex('% '+pattern+' %',postcode),len(pattern))
from address inner join lookup on
address.country = lookup.country
where patindex('% '+pattern+ ' %',postcode)<> 0
And my desire output is
insert address(clean-postcode) values ('3456')
insert address(clean-postcode) values ('DK 3215')
Insert address(clean-postcode) values ('3276')
February 12, 2011 at 12:02 pm
1) are the spaces ( after % and before the second % ) in your patindex needed ?
2) I would use datalength(pattern) in stead of len.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2011 at 1:13 pm
Thank you for the reply.
The spaces in '% '+pattern +' %' are needed. Because if postcode is 764356, and if the search is like '%'+pattern +'%' the control will pick up 7643 from the wrong string as there exists a pattern in postcode.
I dont have SQL Server installed at the moment, if I give the datalength(pattern) ,will I get the clean_postcode as
DK 3215 for the postcode DK 3215 instead of 3215?
February 12, 2011 at 5:32 pm
Ok, so there are some problems with the code that need addressing.
First, the patterns : they will not work with PATINDEX, also we need to know the length of the matched string, so I have added a new column to the lookup table to hold the length of the match expected for each pattern.
So, here are some replacement test lookups:
create table lookup(country nvarchar(100), pattern nvarchar(100),pattern_length smallint)
INSERT INTO lookup (country, pattern, pattern_length) VALUES ('Denmark', '[0-9][0-9][0-9][0-9]',4)
INSERT INTO lookup (country, pattern, pattern_length) VALUES ('Denmark', '[A-Z][A-Z] [0-9][0-9][0-9][0-9]',7)
Now, the update is a classic example of how an UPDATE .. FROM syntax can lead to problems (you have dodged the Celko scolding this time!)
You have multiple matches in the lookups table, so you are getting multiple updates on one row in the address table.
To eliminate that problem I have re-written the update to pull in the pattern that matches with the longest length for each address row.
I have also replaced UPDATE ... FROM in the query and used EXISTS in the WHERE clause, which means you can only ever update each address row once and only when it has a clean postcode to use.
UPDATE address
SET
clean_postcode =
(
SELECT TOP 1
substring (postcode,
patindex ('% ' + pattern + ' %', ' ' + postcode + ' '), pattern_length)
FROM
lookup
WHERE
address.country = lookup.country
AND
patindex ('% ' + pattern + ' %', ' ' + postcode + ' ') <> 0
ORDER BY
pattern_length DESC
)
WHERE EXISTS
(
SELECT
1
FROM
lookup
WHERE
address.country = lookup.country
AND
patindex ('% ' + pattern + ' %', ' ' + postcode + ' ') <> 0
)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 14, 2011 at 3:53 am
Thank that works.
Thnaks for your help
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply