June 28, 2005 at 10:41 am
I have a data base that has a VARCHAR Street Address field that contains the House Number or Street Number with the Street Name. I want to create two separate data fields one with Street or House Number and another field with the Street Name.
Any suggestions
June 28, 2005 at 10:47 am
Here is a function from one of the members of this site. Once you figure it out, you should be able to reverse to only get the letters rather than the numbers.
This should work for splitting up your fields, as long as things like 123 2nd Street aren't entries... You will have to do quite a bit of checking before finalizing your data...
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Numbers]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Numbers]
GO
CREATE TABLE [dbo].[Numbers]( [PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED( [PkNumber]) ON [PRIMARY] ) ON [PRIMARY]
GO
-----------------------------------------------------------------------------------------
DECLARE @i as int
SET @i = 0
WHILE @i < 8000
BEGIN
INSERT INTO dbo.Numbers DEFAULT VALUES
SET @i = @i + 1
END
GO
-----------------------------------------------------------------------------------------
-- Remi Gregoire function
CREATE FUNCTION dbo.RemoveChars (@Input as varchar(8000))
RETURNS VARCHAR(8000)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Return AS varchar(8000)
SET @Return = ''
SELECT @Return = @Return + SUBSTRING( @Input, PkNumber, 1)
FROM dbo.Numbers
WHERE ASCII( SUBSTRING( @Input, PkNumber, 1)) BETWEEN 48 AND 57
AND PkNumber <= LEN( @Input)
RETURN @Return
END
GO
-----------------------------------------------------------------------------------------
SELECT dbo.RemoveChars('l3l45kjhsf87y3')
GO
-----------------------------------------------------------------------------------------
DROP Function RemoveChars
GO
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Numbers]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Numbers]
GO
I wasn't born stupid - I had to study.
June 28, 2005 at 10:51 am
You could use string functions to seperate the string, but from experience this is a pain when you have ambiguous addresses ie: the larks 25 something street.
A better solution is to use some kind of paf software that will give you the address details based on postcode or zip. When the codes are passed to the software you can then strip out anything that does not match (the software does not give you house number) and this should be the house number.
June 28, 2005 at 10:55 am
That's a pretty good idea. Having done this kind of thing before, it really is a booger to strip out the correct information.
I would seriously give andrewkane17's suggestion thought.
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply