Concatenate multiple columns in SQL Server -


i have address information parsed several columns , i'm trying concatenate columns 1 no leading or trailing spaces using view. have tried ltrim , rtrim can't seem figure out how rid of leading/trailing spaces.

i using sql server 2008 r2. below sample of how our data stored. address column output i'm looking/hoping get.

thanks in advance!

houseno|predir|stname|suftype|sufdir|unittype|unitno|city    |state|zip  |address| 123    |      |main  |st     |      |        |      |anytown |ca   |55555|123 main st anytown, ca 55555 245    |      |3rd   |ave    |ne    |        |      |anytown |ca   |55555|245 3rd ave ne anytown, ca 55555 4675   |w     |elm   |dr     |      |        |      |anytown |ca   |55555|4675 w elm dr anytown, ca 55555 789    |e     |1st   |st     |      |apt     |5     |anytown |ca   |55555|789 e 1st st apt 5 anytown, ca 55555 432    |      |locust|blvd   |se    |        |      |anytown |ca   |55555|432 locust blvd se anytown, ca 55555

what want combination of string concatenation , coalesce operator. coalesce returns first non-null value of comma-separated list of expressions.

example:

set concat_null_yields_null on; select houseno + coalesce(' ' + predir, '') + coalesce(' ' + stname, '') address yourtable

will result in:

address 123 main 245 3rd 4675 w elm 789 e 1st 432 locust

note above requires concat_null_yields_null on. if unsure if on connection, call set concat_null_yields_null on before select. concat_null_yields_null off default unless has been set otherwise database (rare).

with concat_null_yields_null on, adding ' ' + predir result in null records have null in predir column, result in coalesce using next value, '', empty string , nothing.


Comments

Post a Comment

Popular posts from this blog

javascript - jquery or ashx not working -

i using ajax file upload control. it creating html: <form action="/pages/handlers/asyncfileuploadhandler.ashx" method="post" enctype="multipart/form-data" target="ajaxuploader-iframe-1397144545"> <input type="hidden" name="type" value="mindvoice"> <input type="file" id="imagefile" style="display: block; width: 80%; margin-right: auto; margin-left: auto;"> </form> and target is: <iframe width="0" height="0" style="display:none;" name="ajaxuploader-iframe-1397144545" id="ajaxuploader-iframe-1397144545"></iframe> the form submitted using .submit() . my ashx handler: <%@ webhandler language="c#" class="asyncfileuploadhandler" %> using system; using system.web; using nettpals.core; public class asyncfileuploadhandler : ihttphandler { public void processre...
Read more

opencv - DataType<cv::detail::deriv_type>::depth what is it used for -

i wondering why line used in lucas kanade tracker in opencv: datatype<cv::detail::deriv_type>::depth can explain me? in opencv, depth of mat refers type of data contained in mat 's data buffer. represented integer values correspond given data type. these integers commonly abstracted appropriate macro definition (e.g. uchar data represented macro cv_8u ). cv::datatype type-traits class provides method obtain corresponding integer value without having memorize macro means data type. there few cases user code needs use datatype::depth . more common datatype::type . a simple example shows 1 possible use of datatype::depth : cv::mat uchar_data = cv::mat::ones(3, 3, cv_8uc1); cv::mat float_data; uchar_data.convertto(float_data, cv::datatype<float>::depth); // ^^ equivalently replaced // cv_32f macro float_data.at<float>(0,1) += 0.5f; std::cout << float_data << std::endl;...
Read more

python 3.x - Mapping specific letters onto a list of words -

can please suggest approach write piece of codes automatically maps letter in letter_str onto dic_key (a dict key string type contains dashes match length of words in word_lst)? so, mapping occurs if letter appears in every words in list @ same position no matter how many words in list. if no letter appears @ position words in word list new_dic_key '----'. please see examples below thanks word_lst = ['ague', 'bute', 'byre', 'came', 'case', 'doze'] dic_key = '----' letters_str ='abcdefghijklmnopqrstuvwxyz' new_dic_key = '---e' if word_list = ['bute', 'byre'] new_dic_key = 'b--e' or word_list = ['drek', 'drew', 'dyes'] new_dic_key = 'd-e-' if words in word_list of same length code give want: word_list = ['drek', 'drew', 'dyes'] cols = [] in range(len(word_list[0])): cols.append([]) word in ...
Read more