473,320 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ISNULL Function and String Concatenation

Dear Group

Just wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL?

SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')

Thanks very much for your expertise and efforts!

Best Regards,

Martin
Jul 20 '05 #1
6 10950
SET @String3 = ISNULL(@String1 + CHAR(32),'') + ISNULL(@String2,'')

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
Try:

SET @String3 = ISNULL(@String1 + CHAR(32),'') + ISNULL(@String2,'')

Alternatively:

SET @String3 = ISNULL(@String1 + ' ','') + ISNULL(@String2,'')

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Martin" <th************@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
Dear Group

Just wondered how I can avoid the CHAR(32) to be inserted if @String1 is
NULL?

SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')

Thanks very much for your expertise and efforts!

Best Regards,

Martin

Jul 20 '05 #3
Thanks Guys!

I actually was thinking about this but didn't try as I was the opinion
that @String1 + CHAR(32) would be evaluated first and always result in
a space even if String1 is NULL. But it actually makes sense as NULL +
something always results in NULL and therefore the condition is true.

However, it doesn't work for a string of length zero only NULL
strings. Any ideas?

Have a nice day!

Martin

th************@hotmail.com (Martin) wrote in message news:<72**************************@posting.google. com>...
Dear Group

Just wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL?

SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')

Thanks very much for your expertise and efforts!

Best Regards,

Martin

Jul 20 '05 #4
Anybody try the obvious?

SET @String3 = RTRIM(LTRIM(ISNULL(@String1,' ') + CHAR(32) +
ISNULL(@String2,' ')))

Danny

"Martin" <th************@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
Thanks Guys!

I actually was thinking about this but didn't try as I was the opinion
that @String1 + CHAR(32) would be evaluated first and always result in
a space even if String1 is NULL. But it actually makes sense as NULL +
something always results in NULL and therefore the condition is true.

However, it doesn't work for a string of length zero only NULL
strings. Any ideas?

Have a nice day!

Martin

th************@hotmail.com (Martin) wrote in message
news:<72**************************@posting.google. com>...
Dear Group

Just wondered how I can avoid the CHAR(32) to be inserted if @String1 is
NULL?

SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')

Thanks very much for your expertise and efforts!

Best Regards,

Martin

Jul 20 '05 #5
SET @String3 = ISNULL(NULLIF(@String1,'') + CHAR(32),'') +
ISNULL(@String2,'')

--
David Portas
SQL Server MVP
--
Jul 20 '05 #6
Thanks Guys! I appreciate your help!
Have a nice day!
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jonas Galvez | last post by:
Is it true that joining the string elements of a list is faster than concatenating them via the '+' operator? "".join() vs 'a'+'b'+'c' If so, can anyone explain why?
2
by: Bob Cottis | last post by:
I am getting wierd behaviour with IsNull in ASP. I am passing a string (which may be null) to a function. When the string is null, IsNull seems to return false the first time it is called, then...
8
by: NickName | last post by:
Hi, I seemed to me IsNull Evaluation within EXEC fails. Here's some more detail of the problem. -- goal: provide one parameter (of various value) to generate a -- report declare @col4...
6
by: Eric J Owens | last post by:
TIA! I recently moved some forms from an a2k mdb file to an a2k adp. There is now an error when opening one of the forms 'the isnull function requires 2 arguments', but I only find references...
17
by: Nathan Given | last post by:
Hello All, I am trying to debug a broken query. The query uses Left$(,4) instead of Left(,4). What is the difference between the Left() and Left$() functions in Microsoft Access? Thanks!...
35
by: michael.casey | last post by:
The purpose of this post is to obtain the communities opinion of the usefulness, efficiency, and most importantly the correctness of this small piece of code. I thank everyone in advance for your...
1
by: rw | last post by:
I have a Microsoft C/C++ compiler version 12.00.8168 installed on my PC running Windows 2000. I have no problem to use the MS C/C++ compiler to compile the following 'C' program and get an...
4
by: briancfk | last post by:
I now converting vb6 code to vb.net code Let me descrip my problem first i got a Function e.g. Public Function Method1(Byval ar as object, Optional ByVal strWHFrom As String, Optional ByVal...
12
by: Richard Lewis Haggard | last post by:
I thought that the whole point of StringBuilder was that it was supposed to be a faster way of building strings than string. However, I just put together a simple little application to do a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.