• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Concatenated string as parameter

I have a concatenated field that includes Address fields and correspondence address fields.

e.g. addressline1+' '+addressline2+' '+postcode+' '+correspondence_address1+' '+correspondence+address2+' '+correspondence_postcode as fulladdress

This field is used as a parameter in a report.  users must enter a character when prompted.  The problem I am having is that sometimes a correspondence address is null but the addressline1 and addressline2 would always have a value.

As an example if I enter 'b' in the search test box and  'fulladdress'  contains 'b', data is returned but when correspondence_address1+' '+correspondence+address2+' '+correspondence_postcode  is null, no data is returned

I have tried replacing the null value with spaces but this has not worked.

Please can anyone suggest a ny wok arounds?  

  • 2
1 Solution
Jerry MillerCommented:
I wouldn't use a concatenated field as a parameter. Instead use all of them as individual parameters and allow them to be null.

@addressline1 vachar(50) = null for example in the declaration.

Then use where addressline1 = ISNull(@addressline1, address line) for each variable

This gives the flexibility for any of the possible null values to be ignored in the query.
turaksAuthor Commented:
Thanks for your response.

Going by your suggestion means that I would have 10 prompts.  It wouldn't look neat having to enter the same search text in 10 boxes.
Jerry MillerCommented:
Are your users entering all of this data in 1 textBox? Can you provide your code?

I am talking about having 10 separate variables where you can test each one for null before continuing.
ValentinoVBI ConsultantCommented:
If you concatenate varchar fields in T-SQL, you need to take NULLs into account.  Use the COALESCE function on each field that can be NULL:

SELECT COALESCE(addressline1+' ','') + COALESCE(addressline2+' ', '')
+ COALESCE(postcode+' ','') + COALESCE(correspondence_address1+' ','')
+ COALESCE(correspondence_address2+' ','')
+ COALESCE(correspondence_postcode,'') as fulladdress

Open in new window

Coalesce: http://msdn.microsoft.com/en-us/library/ms190349.aspx
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now