Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 pull direction out of an address?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lmayer4
Starting Member

USA
33 Posts

Posted - 12/02/2013 :  14:27:17  Show Profile  Reply with Quote
Hi there,

I have this so far and I'm sure there is a sexier, smarter way to do this....

select 
LEFT(
COALESCE(
SUBSTRING ( 'N' ,PATINDEX('% N %', '45 S Joy Rd') , 2 ),
SUBSTRING ( 'E' ,PATINDEX('% E %',  '45 S Joy Rd') , 2 ),
SUBSTRING ( 'S' ,PATINDEX('% S %',  '45 S Joy Rd') , 2 ),
SUBSTRING ( 'W' ,PATINDEX('% W %',  '45 S Joy Rd') , 2 ),
SUBSTRING ( 'NE' ,PATINDEX('% NE %',  '45 S Joy Rd') , 2 ),
SUBSTRING ( 'NW' ,PATINDEX('% NW %',  '45 S Joy Rd') , 2 ),
SUBSTRING ( 'SE' ,PATINDEX('% SE %',  '45 S Joy Rd') , 2 ),
SUBSTRING ( 'SW' ,PATINDEX('% SW %',  '45 S Joy Rd') , 2 ))+'  ',2)[Prefix Directional]


First of all it alwsys says N no matter what so thats wrong, but I can't imagine this is the best way to do this.

Any thoughts would be great.

Thanks

Laura

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 12/02/2013 :  14:57:19  Show Profile  Reply with Quote
Perhaps this:

select case 
       when charindex(' N ', '45 S Joy Rd') > 0 then 'N'
       when charindex(' S ', '45 S Joy Rd') > 0 then 'S'
       when charindex(' E ', '45 S Joy Rd') > 0 then 'E'
       when charindex(' W ', '45 S Joy Rd') > 0 then 'W'
       when charindex(' NE ', '45 S Joy Rd') > 0 then 'NE'
       when charindex(' NW ', '45 S Joy Rd') > 0 then 'NW'
       when charindex(' SE ', '45 S Joy Rd') > 0 then 'SE'
       when charindex(' SW ', '45 S Joy Rd') > 0 then 'SW'
       else ''
       end


Be One with the Optimizer
TG
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 12/03/2013 :  07:33:29  Show Profile  Reply with Quote
Not sure what you're trying to perform ...but your code will always return N since Coalesce looks for first non null value and which is "N"

LEFT(
COALESCE
(
SUBSTRING ( 'N' ,PATINDEX('% N %', '45 S Joy Rd') , 2 ), -- means Substring('N',0,2) -- results in N
SUBSTRING ( 'E' ,PATINDEX('% E %', '45 S Joy Rd') , 2 ), --means Substring('E',0,2) -- results in E
SUBSTRING ( 'S' ,PATINDEX('% S %', '45 S Joy Rd') , 2 ), --means Substring('S',0,2) -- results in S
SUBSTRING ( 'W' ,PATINDEX('% W %', '45 S Joy Rd') , 2 ), --means Substring('W',0,2) -- results in W
SUBSTRING ( 'NE' ,PATINDEX('% NE %', '45 S Joy Rd') , 2 ), -- .... Results in NE
SUBSTRING ( 'NW' ,PATINDEX('% NW %', '45 S Joy Rd') , 2 ), -- .... Results in NW
SUBSTRING ( 'SE' ,PATINDEX('% SE %', '45 S Joy Rd') , 2 ), -- .... Results in SE
SUBSTRING ( 'SW' ,PATINDEX('% SW %', '45 S Joy Rd') , 2 ) -- .... Results in SW
) -- end of Coalesce which'll look for first non null value ... and so is N
+ ' ',2 -- will add two white spaces to 'N ' and Left would turn it to be 'N '
) [Prefix Directional]

Cheers
MIK
Go to Top of Page

lmayer4
Starting Member

USA
33 Posts

Posted - 12/05/2013 :  10:34:13  Show Profile  Reply with Quote
Thanks to all for your help. Sorry it so long to thank you, I to rebuild my computer :)

Laura
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000