hey guys,
just wondering if anyone would know how to do this is sql..

i have a field that has an address in it but then i will get a stupid person put a second address in the same field but with lots of spaces... So would anyone know how i can split the field so that the addresses would be in the columns address and address1 ?

Any help would be appreciated..
Cheers
shoddy

something like this? (this is comma delim)

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[UTILfn_Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[UTILfn_Split]
GO

create function dbo.UTILfn_Split(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)

--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter

--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1

--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)

insert into @ValueTable ( [Value]) Values (@NextString)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end

return
end

also, how are you inserting the records? it would be better to validate client side and save hits on the db.

holy f*** mooby, I thought I knew sql...

if you think anything more than a small part of that was sql, you thought wrong

Yeah I suspected that too - don't recognise the language. But using the approach of writing an app to process several queries is the approach I'd use too.

Since your planned delimiter is a space you need to do this in Mooby's example:

set @NextString = LTRIM(substring(@String,1,@Pos - 1) )

to handle multiple spaces separating actual data.

actually, i take that back, better off doing the ltrim on the insert

last edited by natslovR at 20:41:53 17/Mar/08

Yeah mooby how the hell is that going to help him.

Most address forms are like

Address Line 1
Address Line 2
Suburb Postcode


etc etc


You would have to write some tricky string manipulation js to validate whether someone is typing 2 addresses into one field. I've never really heard of that being a common problem to be honest.

Yeah mooby how the hell is that going to help him.

thats what he asked for, a split on a delimte. his spec should be more specific. like i said, it should be validated upstream. if you want to store an address, suburb, postcode, have fields for each and insert accordingly.

if you think anything more than a small part of that was sql, you thought wrong
um.. what?

I didn't see any mention of a specific database server, are you guys just assuming ms sql or something? Aren't there built in string functions so you can just do this with a single sql hit? update table set address1 = somestringfunctions(address), address = somestringfunctions(address)

Another thing that's not clear is how he's going to determine exactly which space comes between what he considers two different addresses - when he says "i have a field that has an address in it but then i will get a stupid person put a second address in the same field but with lots of spaces" does he mean the first address only ever has single spaces between it's sections, but then there's lots of spaces and then another address? If so, the delimiter isn't a space, but a specific number of spaces, or perhaps more than one consecutive space

are you guys just assuming ms sql or something?

what other db is there? :P to be honest, the problem sounds like an architect prob

last edited by mooby at 21:03:41 17/Mar/08

yeah I agree, I'm guessing he just wants to clean up the existing data

my job involves me printing addresses on envelopes and part of the job is the customer sends me an excel spreadsheet with all teh contact details.. the biggest problem we have is the customer inserting 2 addresses into the one field..
for example

Fname joe
Sname blo
Add 55 cutwrist ave 65 stupid drv
Sub dumb
State nsw
Postcode 2958

Thats what it looks like in the excel spreadsheet... we could have 6000 out of 11759 records like that... one part in the process is converting the excel spreadsheet to a database... a program outputs the data like that after we add barcodes... so i thought i would use a query to place the second address in another collumn so that i can place it on a new line when i mail merge into a word doc... Thats the basic story...

And Thanks Mooby for your help... much appreciate.. will try later when im more awake...


(i hope that turned out right....

that didnt turn out right... the address line...

its meant to be
55 first st (lots of spaces....) 65 second st

here's roughly how I would do that if it was mysql - but most databases should have similar string manipulation functions:

update table set address1 = trim(substring(address, locate(" ", address))), address = substring(address, 1, if(locate(" ", address), locate(" ", address)-1, address))

in that query I've just used 3 consecutive spaces as the token to look for - you might have a better idea of what number of spaces to use. and make sure you do the update in that order (address1 and then address)

could always do a vba macro in execel. eaiser to step into.

its meant to be
55 first st (lots of spaces....) 65 second st

do the split on the "lots of spaces". ive marked with a # because double spaces arent shown here.

declare @address varchar(100)
declare @address1 varchar(100)
declare @address2 varchar(100)
declare @postion int
declare @length int

set @address = '55 cutwrist ave#65 stupid drv' --GET ORIGINAL DB FIELD
set @postion = Charindex('#', ltrim(rtrim(@address))) --FIND THE POSTION OF THE DOUBLE SPACE
set @length = len(@address) --FIND THE LENGTH OF THE ADDRESS

set @address1 = substring(ltrim(rtrim(@address)), 1, @postion)
set @address2 = substring(ltrim(rtrim(@address)), @postion, @length)

print @address1
print @address2


last edited by mooby at 01:07:54 18/Mar/08

last edited by mooby at 01:08:56 18/Mar/08

^^ note the double spaces arent shown here.

to post stuff as you type it in the box, i.e. with lots of spaces use <pre>stuff     </pre>

edit:    I'm    dumb   it's    pre

last edited by whoop at 01:31:03 18/Mar/08

omg shoddy! hello!

fully serial, just print the address all on one line

australia post loves address blocks like this

im guessing you're not using dpid (the little barcode above the address) for your addresses, so its all going to be done manually anyway

the average posty can work out carp addresses

ok spook... we are using dpid and also for the record... my job is a division of australia post.... and to get cheaper mail the address has be be printed right... and as such we can't have 2 addresses on one line...

And Thank you all for your help i didnt even think about a vba macro... although i understand vba much better than sql... however i understand sql and can use and modify it not a problem.. will try today and report back...
Thanks again

My advice, don't do this in SQL. It sucks for string handling especially something that I can see as a data quality issue that needed to be sanitise before it's even entering the database.

Use your application to clean this up it's easier and try to get SQL to do it, especially if it's MS SQL Server...

Doing that automatically will get probably deal with about 80% of the abnormal cases. Then you might have to eye ball the rest because you will not be able to carter for all the infinite users stupidity...

So yeah use Macro to parse the raw data first, look for exception cases. Adapt your code to handle that. Rise and repeat.

Until the next batch of data....

PS you could even use Excel Text to Column function to handle most of the bad data. Then see what else you need to deal with, maybe it's VBA code for the rest of the really really bad data.

Personally I would try and hunt down the bad input before getting to SQL. I'd save it as a CSV and use grep,sed,awk,perl to find it.

grep " " addresses.csv > bad.csv
grep -v " " addresses.csv > good.csv

Then open bad.csv and look for reasons for the double space, depending on the size of it you could deal with addresses manually ( cut & paste from bad.csv to good.csv as you fix them or find they're actually okay ) or use grep to further break it down, or get tricker with sed/awk/perl and start regexing at it.

or obviously you can use more native excel/windows tools, whatever you're comfortable with.

ok spook... we are using dpid and also for the record... my job is a division of australia post.... and to get cheaper mail the address has be be printed right... and as such we can't have 2 addresses on one line...

well, the flipside to that is feed all your address info into paflink, its pretty good at working out dpids from bad addresses

i send out all sorts of crud and it gets delivered fine

come here stinky and give us a kiss you sexy man

Ok.. tried the vba macro option.. i've got all the variables working etc... the only problem is this line..
(add2 , 3 are unused variable so far)
add2 = int32.string.substring(add1, 1, pos)
add3 = int32.string.substring(add1, pos, leng)

that code returns object required...

pos is the middle of the two address so therefore add2 should be the first half off add1 and add3 should be from pos to the leng of the string.

add1 has both the address in it separated by lots of space..

any thoughts..??

PS you could even use Excel Text to Column function to handle most of the bad data. Then see what else you need to deal with, maybe it's VBA code for the rest of the really really bad data.

the only problem with this is that the spaces and addresses are not a common width... is that function also in 2003??? we are only using 2003 not 2007

on the topic of sql, I have a mssql box that has some scheduled sql jobs (through sql server agent) that have become orphaned from their parent maintenance plan.

As such I cannot delete them through EM as it says they cannot be deleted as they were created by a MSX server.

Does anyone (and yes I am talking to gnats) have info on deleting orphaned jobs via query analyzer?

kthxbi

nm, updated the originating server field on the jobs via EM and then can delete ok

you're so sexy when you work stuff out crushles

you're so sexy when you work out jim.. i have webcams in your gym shorts

in vba use the split method


dim sAddress1 as string
dim sAddress2 as string


sAddress1 = split("the address", " ")(0)
sAddress2 = split("the address", " ")(1)

Does anyone (and yes I am talking to gnats) have info on deleting orphaned jobs via query analyzer?

not off the top of my head, but have you tried drop jobname?

i don't have sql on me atm but there's an internal override on sp_delete_job... not sure if it still works or not:

sp_delete_job 'jobname', 'sourceserver'

otherwise if this isn't production update sourceserver/originatingserver field in msdb..sysjobs to @@SERVERNAME then just use sp_delete_job like normal

i can't remember if sysjobs is protected or not, so yo may need to do:

sp_configure 'allow updates', 1
reconfigure with override

then run the update.

oh, too slow.. you fixed it already.


last edited by natslovR at 20:55:03 18/Mar/08