retrieve part of an address - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

retrieve part of an address

Author

Topic

junior6202
Starting Member

45 Posts

Posted - 2015-03-25 : 16:25:39

I have a question if I want to just retrieve the numbers left of the space in this address column what can I use? The Address column is Varchar.

Address:
6501 Red Hook Place #201
32 Orchard St
1 Hardy Rd ste 210
379 E Center St.
170 Bridge St
399 Interpace Pkwy
1800 Valley Rd
2210 Harding Hwy
41 Maple Ct

Expected Results:

Address
6501
32
1
379
170
399
1800
2210
41

I tried using this:
Left([ADDRESS], Charindex(' ',[ADDRESS] - 1)) as 'Address'

But i received an error:
Conversion failed when converting the nvarchar value '6501 Red Hook Place #201' to data type int.

Thank you in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-25 : 16:34:15

Are you putting it into an int variable? If so, convert it first.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

junior6202
Starting Member

45 Posts

Posted - 2015-03-26 : 00:15:26

Thanks Tara for your advice. I solve the issue, I found a procedure to retrieve the number out of a string and I used it together with the Left() function and it worked just fine.

Again, Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources