May 7, 2009 at 9:22 am
Using the following SQL script to pull item number and item description from item table in order to create
a csv type file to import data into another application. The item number and item description have quotes (") around each string, i.e. "itemnumber" "itemdescription".
See script:
USE QVI_App
select
CHAR(34) + item.item + CHAR(34) as Item
, CHAR(34) + item.description + CHAR(34) as Description
from item
where item.mps_flag = 0
and substring(item,8,3) <> 'STK'
and substring(item,8,3) <> 'PLT'
and substring(item,8,3) <> 'MCH'
and substring(item,8,3) <> 'ASY'
and substring(item,8,3) <> 'REP'
and substring(item,8,3) <> 'RWK'
order by item
Problem is that some data has extra quotes in it such as example below:
"018246" "LASER DIODE-RED,30MW,655NM,5.6MM D "OBS""
The 'obs' string has an extra quotes in the string.
Looking for a method to add to my extract SQL script to look for and eliminate the extra quotes.
Per example above, result should be "018246" "LASER DIODE-RED,30MW,655NM,5.6MM D OBS"
How do I search for and eliminate the extra quotes?
May 7, 2009 at 9:29 am
try using REPLACE to search for two double-quotes and replace them with one double-quote
SELECT REPLACE('"OBS""','""','"')
May 7, 2009 at 9:54 am
I forgot to add an important piece of information. We do not want to update or fix the data within the SQL table, we do want to fix the data in extracted file. I understand about using the Replace command but don't know how to tell SQL to run the Replace against the extract file - not the SQL table.
How do I code the script to use the Replace command against the extracted data file and not the SQL database table?
May 7, 2009 at 10:00 am
The results from SQL query/script are to a file and or text.
May 7, 2009 at 10:31 am
If you use the replace command in your select query then this will not affect your data at all..
Add the replace into your code that you have and you will be fine
May 7, 2009 at 10:40 am
steveb (5/7/2009)
try using REPLACE to search for two double-quotes and replace them with one double-quote
SELECT REPLACE('"OBS""','""','"')
That won't work.
Try replacing your item.description in the select statement with:
Replace(item.description, '"', '')
I know it's hard to read, but after 1st comma, single quote - double quote - single quote
and after 2nd comma, single quote - double quote
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 7, 2009 at 12:39 pm
Still having problems with getting correct output data extract file. SQL script is pulling item.item and item.description from item table
and putting double quotes around each string - this is working okay. Now want to get rid of extra quotes which the Replace command kind of does, but the output is still wrong. The end result we want is 2 columns: item.item and item.description with a quote (") around each string but with no extra or doulble quotes ("") in the description. Getting close but ending up with too many columns showing
before and after data when we only want the 2 final result columns. Please help :w00t:
Script used:
USE QVI_App
select
CHAR(34) + item.item + CHAR(34) as item
, CHAR(34) + item.description + CHAR(34) as description
, Replace (item.description, '""', '"')
, Replace (item.item, '""', '"')
from item
where item.mps_flag = 0
and substring(item,8,3) 'STK'
and substring(item,8,3) 'PLT'
and substring(item,8,3) 'MCH'
and substring(item,8,3) 'ASY'
and substring(item,8,3) 'REP'
and substring(item,8,3) 'RWK'
order by item.item
Results are now in 4 columns: item number, item description (with extra quote), item description (correct) and item number:
"018246" "LASER DIODE-RED,30MW,655NM,5.6MM D "OBS"" LASER DIODE-RED,30MW,655NM,5.6MM D "OBS" 018246
May 7, 2009 at 12:42 pm
The final result should be:
"018246" "LASER DIODE-RED,30MW,655NM,5.6MM D OBS"
with no extra quotes around OBS part of string as in this example.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply