Trimming text from a field question.

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.

All Forums

Old Forums

CLOSED - General SQL Server

Trimming text from a field question.

Author

Topic

UnkFrank
Starting Member

4 Posts

Posted - 2005-06-04 : 09:28:38

I was wondering if someone could suggest a solution to this problem. I have an address that was imported from another program into MSSQL. The field has the name of the company followed by a colon, the address and then a lot number. Example:

Kimball:14530 Calusa Palms Dr Lot 65

Can anyone suggest a way in SQL to remove "Kimball" the ":" and also "Lot 65"?

There are hundreds of these records.

thanks,

Frank

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-04 : 09:42:51

Well, it's ugly, but the simplest way is using the standard string functions like substring and charindex.

Try this :

Declare @Address varchar(500)

SET @Address = 'Kimball:14530 Calusa Palms Dr Lot 65'

SELECT

SUBSTRING(
SUBSTRING(@Address, CHARINDEX(':', @Address) + 1, Len(@Address)) ,
1, CHARINDEX(' Lot', SUBSTRING(@Address, CHARINDEX(':', @Address) + 1, Len(@Address))) - 1)

To select your records with this method, you can do (assuming your column is called address):

SELECT

SUBSTRING(
SUBSTRING(Address, CHARINDEX(':', Address) + 1, Len(Address)) ,
1, CHARINDEX(' Lot', SUBSTRING(Address, CHARINDEX(':', Address) + 1, Len(Address))) - 1)

FROM MyTable

OR, if you want to update them in the table..

UPDATE MyTable
SET Address =
SUBSTRING(
SUBSTRING(Address, CHARINDEX(':', Address) + 1, Len(Address)) ,
1, CHARINDEX(' Lot', SUBSTRING(Address, CHARINDEX(':', Address) + 1, Len(Address))) - 1)

Another way to do it, might be to do some trickery with the Parsename function, but I'll let you experiment with that. http://www.sqlteam.com/item.asp?ItemID=15044

Hope that helps

Damian
Ita erat quando hic adveni.

UnkFrank
Starting Member

4 Posts

Posted - 2005-06-04 : 09:49:49

Damian,
Thanks for the quick reply. I'll give it a try and let you know how it turns out.
I'm assuming I do all of this in Enterprise Manager?
Thanks,
Frank

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-04 : 09:51:40

Noooooooooooooooooooooooooooooo

Query Analyzer is where you want to live.

Damian
Ita erat quando hic adveni.

UnkFrank
Starting Member

4 Posts

Posted - 2005-06-04 : 09:52:28

Also, these Company names are all diffent. Kimball might be 50 of them, Jones could be another 100. Does this affect your sql?
Thanks,
Frank

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-04 : 09:54:00

Experiment, it should work, but you might need to tweak it a little.
I'm good, but not omnipotent

Damian
Ita erat quando hic adveni.

UnkFrank
Starting Member

4 Posts

Posted - 2005-06-04 : 11:24:09

Damian,
Since I have no idea how to use or adapt your piece of code(not your fault, just new to this), how about approaching it from another way?
Is there a way to do a Set Update where I can delete or remove the "company_name:" which precedes the address from the begining of the jobaddress field?
By doing it this way I could replace "company_name:" each time and modify those records in one pass.
Then remove the "Lot number" at another time.
Thanks again,
Frank

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-04 : 21:08:05

Sorry Frank, that doesn't make much sense. I've tried to guess your requirements and keep getting it wrong, it's no fun for me

.

With the UPDATE statement, the CHARINDEX function and the SUBSTRING function you can do what you need to do. Open up Books Online and read up on those things and experiment with it. We're here to help, but we can't do your job for you.

Damian
Ita erat quando hic adveni.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources