pull direction out of an address? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2012 Forums

Transact-SQL (2012)

pull direction out of an address?

Author

Topic

lmayer4
Starting Member

33 Posts

Posted - 2013-12-02 : 14:27:17

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 14:57:19

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-12-03 : 07:33:29

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

lmayer4
Starting Member

33 Posts

Posted - 2013-12-05 : 10:34:13

Thanks to all for your help. Sorry it so long to thank you, I to rebuild my computer :)

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources