SQL Server Forums
Profile | Register | 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?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lmayer4
Starting Member

USA
31 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
6059 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
1052 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
31 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  
 New 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.03 seconds. Powered By: Snitz Forums 2000