Converting mysql query to MS SQL

Question

Hi I wonder if someone can help me, I have the following MySQL query (extract):

concat_ws('\n', 
                    IF(LENGTH(PafAddress.line1),PafAddress.line1,NULL),
                    IF(LENGTH(PafAddress.line2),PafAddress.line2,NULL),
                    IF(LENGTH(PafAddress.line3),PafAddress.line3,NULL),
                    IF(LENGTH(PafAddress.line4),PafAddress.line4,NULL),
                    IF(LENGTH(PafAddress.Line5),PafAddress.line5,NULL),
                    IF(LENGTH(PafAddress.post_town),PafAddress.post_town,NULL),
                    IF(LENGTH(PafAddress.county),PafAddress.county,NULL),
                    IF(LENGTH(PafAddress.postcode),PafAddress.postcode,NULL)
) AS AddressLabel

Which takes multiple address fields from another table and puts it into a memofield format, with line breaks, and removing null address lines.

Works perfectly in MySQL.

I am now trying to implement this onto a PHP website which is run by MS Access (not my choice!) and it doesnt like the syntax.

I'm not a MS Access expert so would appreciate someone telling me how this might be achieved in Access SQL.

Please, read this: stackoverflow.com/help/how-to-ask. Now, what did you try? What links on Google did you visit? — Chilion
– Chilion, Commented Feb 26, 2015 at 8:28

Bill · Accepted Answer · 2015-03-03 14:00:43Z

1

Have a try at using the IIF(condition, truevalue, falsevalue) statement. Also, you can concatenate using the &

Here are two references for you: article about using IIF and article about concatenating using & and article about len() instead of length()

so your code may look something like this:

IIF(Len(PafAddress.line1)>0,PafAddress.line1,"") & " " &
IIF(Len(PafAddress.line2)>0,PafAddress.line2,"") & " " &
IIF(Len(PafAddress.line3)>0,PafAddress.line3,"") & " " &
IIF(Len(PafAddress.line4)>0,PafAddress.line4,"") & " " &
IIF(Len(PafAddress.Line5)>0,PafAddress.line5,"") & " " &
IIF(Len(PafAddress.post_town)>0,PafAddress.post_town,"") & " " &
IIF(Len(PafAddress.county)>0,PafAddress.county,"") & " " &
IIF(Len(PafAddress.postcode)>0,PafAddress.postcode,"") 
AS AddressLabel

To skip null records, try using this:

iif( Len(PafAddress.line1)>0 
     OR Len(PafAddress.line2)>0
     OR Len(PafAddress.line3)>0
     OR Len(PafAddress.line4)>0
     OR Len(PafAddress.line5)>0
     OR Len(PafAddress.post_town)>0
     OR Len(PafAddress.county)>0
     OR Len(PafAddress.postcode)>0 
,
IIF(Len(PafAddress.line1)>0,PafAddress.line1,"") & " " &
IIF(Len(PafAddress.line2)>0,PafAddress.line2,"") & " " &
IIF(Len(PafAddress.line3)>0,PafAddress.line3,"") & " " &
IIF(Len(PafAddress.line4)>0,PafAddress.line4,"") & " " &
IIF(Len(PafAddress.Line5)>0,PafAddress.line5,"") & " " &
IIF(Len(PafAddress.post_town)>0,PafAddress.post_town,"") & " " &
IIF(Len(PafAddress.county)>0,PafAddress.county,"") & " " &
IIF(Len(PafAddress.postcode)>0,PafAddress.postcode,"" & char(13) & char(10) )
,"") 
AS AddressLabel

In this query, (1st) check if any value's length is greater than 0...if so, output the address line with CRLF; otherwise output an empty string.

edited Mar 3, 2015 at 14:00

answered Feb 26, 2015 at 13:58

Bill

3141 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathan Green Over a year ago

Worked great thanks, but doesnt introduce line breaks after each line?

Bill Over a year ago

@Jonathan Green try adding ' & Chr(13) & Chr(10) ' anywhere you want a line break in access query. additional info link

Bill Over a year ago

@Jonathan Green I'm not sure what you mean. Maybe you are referring to the null records. If all fields are empty, the returned value would be empty except for the spaces added by the & " " & part of the statement. So you could use the trim() function: trim(iif(len(PafAd.......postcode,"")) as AddressLabel. You can then wrap that in an iif statement. I'll edit the answer I posted to include this.

Collectives™ on Stack Overflow

Converting mysql query to MS SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related