Concatenated string as parameter

Posted on 2012-09-21
Last Modified: 2012-11-05
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?  

Question by:turaks
    LVL 18

    Expert Comment

    by:Jerry Miller
    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.

    Author Comment

    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.
    LVL 18

    Expert Comment

    by:Jerry Miller
    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.
    LVL 37

    Accepted Solution

    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


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now