pull data from a sql string

Question

So the problem i am having is i have a string of data, address, and i need to pull just the city out to compare to another location

example of Table

Fname     lname       address

John        Smith       731 Fondren, Houston, TX

 

i need to pull houston to compare it to another table for location... dont know if i am explaining this correctly... if i can clear up my question just let me know... its a rather small amount of data... about 10 entries...

 

i have tried SELECT CAST(SUBSTRING(@Address, CHARINDEX(',', @Address) + 1, 8) AS address)
from employee
where fname = 'john';
 but i could be doing something completely wrong ...

 

now the 8 is just a place holder til i get it 2 work and can change it to where it will find the second ','

 

i am a complete novice to everything sql

Answer 1

I have a blog post showing how to parse addresses. Take a look here

Parsing the Address field to its individual components

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

If you are 100% sure about the address format and the commas like (street, City, State, Zip) then you can use following logic:

 

select '731 Fondren, Houston, TX, 80247' as addr, 
	parsename(replace('731 Fondren, Houston, TX, 80247',',','.'),4) as street,
	parsename(replace('731 Fondren, Houston, TX, 80247',',','.'),3) as City,
	parsename(replace('731 Fondren, Houston, TX, 80247',',','.'),2) as State,
	parsename(replace('731 Fondren, Houston, TX, 80247',',','.'),1) as Zip

PARSENAME() is generally used to parse the parts from a fully qualified object name, like a table, check this link: http://msdn.microsoft.com/en-us/library/ms188006.aspx

---

~Manu
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 3

another thing i have tried was

 

select lname, dlocation

from employee, dept_location

where dno=dnumber and address like ‘*’ || dlocation||, ‘*’;

 

 

but i get invalid character...

 

and i need 2 do everything as a query, all the inputs are the same

Answer 4

where dno=dnumber and address like ‘*’ || dlocation||, ‘*’;

Now whats this: address like ‘*’ || dlocation||, ‘*’;

Are you working with any front end language?

Check your query from SSMS/Query analyzer first, replace ‘*’ with '%' and dlocation with the variable or match keyword.

---

~Manu
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 5

im using oracle database 10g expresss, which uses their website other then that I'm not sure

 

dlocation is the location of the dept and I'm comparing that to the address

Answer 6

Kyle, if you are using Oracle 10g then this is not the right place you are seeking advice.

Please check any Oracle forum.

---

~Manu
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 7

i didnt know there was any different thing for sql... i thought sql was sql but i guess i am wrong

Answer 8

No problem Kyle.

You can post your questions here in Oracle PL/SQL forums: http://forums.oracle.com/forums/forum.jspa?forumID=260

---

~Manu
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 9

T-SQL is not the same as PL SQL and so often the T-SQL query will not work the same way in other languages.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 10

Basic SQL queries (ANSI SQL) are the same.

The following link explains some of the difference between SQL Server and ORACLE SQL:

Migrating from Oracle to SQL Server

In Microsoft public newsgroups, I've been observing a recent increase in the number of questions that deal with migrating from Oracle to SQL Server. It is not an easy task to migrate your Oracle database and applications to SQL Server, as there are major architectural differences between SQL Server and Oracle. Further, Oracle's PL/SQL is vastly different from Microsoft's Transact-SQL (T-SQL).

http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm

---

Kalman Toth, Admin/Prog, SSAS, SSIS, SSRS; SQL 2008 GRAND SLAM