Solved

Quick SQL Systax question

Posted on 2006-10-30
11
231 Views
Last Modified: 2008-03-04
I was wondering how to write a SQL statement:        

                UCASE all string
o      TRIM all white space
o      Replace ; with :
o      Replace & with AND
o      If value is blank or null set it to N/A

and seperatly how would I write this?

o      If value is blank or null set it to blank

and this is also sperate how would I write this?

o      If value is blank or null set it to 0

I would look it up but I am pressed for time and cant find any exact scripts...
Thanks!

Yaba
0
Comment
Question by:yabakingda
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17834392
UCASE(LTRIM(RTRIM(REPLACE(REPLACE( CASE WHEN field IS NULL THEN 'N/A' WHEN field = '' THEN 'N/A' ELSE field END  , '&', 'AND') , ';', ':'))))
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17834407
update tablename set columnname = case when '' then 'N/A' when null then 'N/A' else replace(ltrim(rtrim(upper(columnname))),'&',' and ')  end case

update tablename set columnname = case when '' then 0 when null then 0 else replace(ltrim(rtrim(upper(columnname))),'&',' and ')  end case
update tablename set columnname = case when '' then '' when null then '' else replace(ltrim(rtrim(upper(columnname))),'&',' and ')  end case
 
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17834427
sorry... didn't parse it ... missing the columnname after the word case  

update tablename set columnname = case columnname when '' then 'N/A' when null  then 'N/A' else replace(ltrim(rtrim(upper(columnname))),'&',' and ')  end  


0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 17835062
The following is for your first case:
replace(replace(upper(ifnull(trim(field),'N/A')),'&','AND'),';',':')
However, you said "TRIM all white space" and trim only deals with leading and trailing characters (default blanks). To remove any blanks "in the middle" add another replace statement -
replace(replace(replace(upper(ifnull(trim(field),'N/A')),'&','AND'),';',':'),' ','')

>>If value is blank or null set to blank<<
set field = (isnull(trim(field),' ')

>>If value is blank or null set to 0<<
set field = (isnull(trim(field),'0')

To avoid update of all field add where condition
where isnull(trim(field)) = 1
0
 
LVL 32

Expert Comment

by:awking00
ID: 17835105
Sorry, isnull and ifnull can get confusing. The last two should have been
>>If value is blank or null set to blank<<
set field = (ifnull(trim(field),' ')

>>If value is blank or null set to 0<<
set field = (ifnull(trim(field),'0')

The where condition is, however,
where isnull(trim)field)) = 1
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17835979
>>Sorry, isnull and ifnull can get confusing. <<
I suspect you mean NULLIF
0
 
LVL 32

Expert Comment

by:awking00
ID: 17836555
acperkins,
I do not mean nullif. Nullif syntax is nullif(expr1, expr2) - returns null if expr1 = expr2, otherwise returns expr1. In this case we do not want to return null, we want to return blank or zero if the value of the expression is null.
0
 
LVL 32

Expert Comment

by:awking00
ID: 17836657
Maybe a literal interpretation of my query would help.
trim(field) removes leading and trailing blanks from field. If field is blank, then trim(field) is null and, if field is null, then trim(field) is also null. So ifnull(trim(field),'N/A/) in either of those cases returns 'N/A' otherwise it will return the value of field trimmed, which is then set to upper case, replaces any ampersand ('&') with the letters 'AND' and replaces any semi-colon with a colon. Furthermore, if there are any blanks between the first and last characters of field, the added replace(...,' ','') will remove them.

Likewise ifnull(trim(field),' ') returns a blank if field is blank or null and ifnull(trim(field),'0') returns a character zero if the field is blank or null. It may be that the latter case requires a conversion if a number is required.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17837453
>> TRIM all white space <<

This will require a custom function.  Everything else can be done easily using standard SQL functions (as shown by earlier posts).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17839784
awking00,

>>I do not mean nullif. <<
Simply put, there is no IFNULL in T-SQL (the SQL dialect in this Topic Area), perhaps you are thinking of PostgreSQL.

In any case, it may be  moot point as the questioner is aparently using MS Access and not MS SQL Server.
0
 
LVL 32

Expert Comment

by:awking00
ID: 17841361
acperkins,
I apologize for that. I was thinking I was in the mysql section.
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

624 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