Separating string into first name, last name, address, etc

Question

Basically, I have to separate the following strings into the correct columns:

'Anna Campbell Flat 9 1153B Great South Road Epsom Auckland 1050'

'Paul Campbell Flat 123 11 Ongly Ave Epsom Auckland 1050'

I figure that all I need to do is declare variables, set each variable to a different part, and insert into the correct columns, I'm just not sure how to tell the DB what each part is.

I have to insert it into the customer_address field, and need to separate it into first_name, last_name, customer_name, street_number, street_name, and customer_address.

All I've got so far are the variables:

declare @firstname varchar(50)
declare @lastname varchar(50)
declare @customername varchar(100)
declare @streetnumber varchar(50)
declare @streetname varchar(50)
declare @customeraddress varchar(50)

Do you only have to split that one string? Or do you have a dataset full of similar strings, which all need to be split? — DBro, May 02 '19 at 01:35
Just this one and one more that's the same format, just a different first name and address. — Belkan_Pride, May 02 '19 at 01:35
There are a good number of questions here on StackOverflow that ask this type of question, which you can browse for clues. Several ways to skin this cat. Basically, the main approach would be to do a `WHILE` loop, looking for spaces in the string. Each time through the loop you would know the starting and ending position (index) for the next word in the string. See example: [link](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — DBro, May 02 '19 at 01:49
Also, I'd encourage you to use more descriptive variable names in your script, for the sake of read-ability. For instance, `DECLARE @FirstName varchar(50)`, etc. — DBro, May 02 '19 at 01:51
Are you saying that you literally only have to parse two strings, and you know what those strings are? — David Dubois, May 02 '19 at 03:11
If so, then just assign constants. set @FN='Anna'; set @LN='Campbell'; and so on. — David Dubois, May 02 '19 at 03:20
It is more or less that, but I need to write procedures and such to separate them, rather than do it manually. — Belkan_Pride, May 02 '19 at 04:11
My mistake. The second string is more different than I realised. I've edited the original question to include it. — Belkan_Pride, May 02 '19 at 22:57

score 1 · Answer 1 · answered May 02 '19 at 02:49

One way to do this is to use a function and pass to that function a section of text to read, the delimiter and the test .

I use the following function for things like your requirement..

First - create a function like the following

CREATE FUNCTION [dbo].[fnParseString]
(
    @Section SMALLINT,
    @Delimiter CHAR,
    @Text varchar(1000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
    DECLARE @NextPos SMALLINT,
        @LastPos SMALLINT,
        @Found SMALLINT

    IF @Section > 0
        SELECT  @Text = REVERSE(@Text)

    SELECT  @NextPos = CHARINDEX(@Delimiter, @Text, 1),
        @LastPos = 0,
        @Found = 1

    WHILE @NextPos > 0 AND ABS(@Section) <> @Found
        SELECT  @LastPos = @NextPos,
            @NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1),
            @Found = @Found + 1

    RETURN  CASE
            WHEN @Found <> ABS(@Section) OR @Section = 0 THEN NULL
            WHEN @Section > 0 THEN REVERSE(SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END))
            ELSE SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END)
        END
END

Then use it like this :

select [dbo].[fnParseString] (11,'','Anna Campbell Flat 9 1153B Great South Road Epsom Auckland 1050') as first_name

Anna.

 select [dbo].[fnParseString] (10,'','Anna Campbell Flat 9 1153B Great South Road Epsom Auckland 1050')  as last_name

Campbell

you get the idea.. It's just one way of doing it.. and not a fool proof way of getting names and addresses as you may have more on less spaces in an address.. Great South and GreatSouth will yield different results..

Gave that a shot, worked well, but what I didn't realise was that the other string is one word longer, so it works for the string I specified, but not the second one. I'll update the question to make things clearer. — Belkan_Pride, May 02 '19 at 22:45

score 0 · Answer 2 · answered May 02 '19 at 03:05

What about Billy Bob Thornton? What about someone that lives in New Plymouth? Parsing names alone is very difficult; combining that with addresses increases the problem exponentially.

You could write something that parses a majority of the cases using common patterns (two-word names, one-name cities, etc. but you're still going to have a LOT of manual cleanup to do. If at all possible, I would try and solve this at the heart of the problem, getting data that's properly separated already rather then trying to parse it. Either that or live with a lot of junk data.

I agree, but it's what I've been given to work with for the assignment. It's messy, and kinda triggering for me, but it is what it is. lol — Belkan_Pride, May 02 '19 at 03:41

Separating string into first name, last name, address, etc

2 Answers2