yabakingda
asked on
Quick SQL Systax question
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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
UCASE(LTRIM(RTRIM(REPLACE(
update tablename set columnname = case when '' then 'N/A' when null then 'N/A' else replace(ltrim(rtrim(upper(
update tablename set columnname = case when '' then 0 when null then 0 else replace(ltrim(rtrim(upper(
update tablename set columnname = case when '' then '' when null then '' else replace(ltrim(rtrim(upper(
update tablename set columnname = case when '' then 0 when null then 0 else replace(ltrim(rtrim(upper(
update tablename set columnname = case when '' then '' when null then '' else replace(ltrim(rtrim(upper(
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(
update tablename set columnname = case columnname when '' then 'N/A' when null then 'N/A' else replace(ltrim(rtrim(upper(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
>>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
>>Sorry, isnull and ifnull can get confusing. <<
I suspect you mean NULLIF
I suspect you mean NULLIF
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.
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.
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.
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.
>> 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).
This will require a custom function. Everything else can be done easily using standard SQL functions (as shown by earlier posts).
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.
>>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.
acperkins,
I apologize for that. I was thinking I was in the mysql section.
I apologize for that. I was thinking I was in the mysql section.