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

Replace empty string with No Data

I have a field1 text type in MS access table1 that could have empty string in.

I need to create select statement that will work in Access and SQL Server to replace empty string to “No Data”
Something as replace function, however replace is not working in SQL Server, not sure with Nz too.

Any idea?
0
Taras
Asked:
Taras
  • 4
  • 3
1 Solution
 
PortletPaulfreelancerCommented:
does "empty string" mean "null"? (I will assume it does.)

syntax differences between the 2 products may preclude a single solution

sql server:

isnull([field1],'no data')
case when [field1] is null then 'no data' else [field1] end

in access: (I think, not my strength)

nz([field1],"no data")
iif(isnull([field1]),"no data",[field1])
0
 
TarasAuthor Commented:
Empty string means "" not Null
0
 
PortletPaulfreelancerCommented:
by the way, you should include sql server as a topic if asking sql server related questions
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
PortletPaulfreelancerCommented:
ok, my bad - wasn't sure

I think you face the same sort of issues however in that syntax differs between the 2 products.

sql server:

case when [field1] ='' then 'no data' else [field1] end

in access: (I think, not my strength)

iif([field1]="","no data",[field1])
0
 
TarasAuthor Commented:
I need Select statment.
I know for IIF(condition,true,false)
0
 
PortletPaulfreelancerCommented:
>>I need to create select statement that will work in Access and SQL Server to replace empty string to “No Data”

I am unable to suggest one that will work in both - which was my point really. Perhaps someone else can.

select -- sql server
case when [field1] ='' then 'no data' else [field1] end as field1
from [dbo].[table1]

select -- access, I think
iif([field1]="","no data",[field1]) field1
from [table1]

I'd be more than happy to provide a more comprehensive query, but I only know of [field1] & [table1]

{+ edit sorry a typo fixed}
0
 
TarasAuthor Commented:
Thanks a lot
0
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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