REPLACE_STR Not Working!!!RSS

2 replies

Last post Mar 21, 2007 12:01 PM by Anonymous

  • REPLACE_STR Not Working!!!

    Mar 20, 2007 02:08 PM|Anonymous|LINK

    Hi All,
    I am new here and to LogParser so please be gentle.

    I am attempting to use LogParser to create a chart from an authentication log which is CSV. I have the query counting all of the unique values in a specific field, and the output is a nice little graph chart. In this specific field there are three reoccurring values and one of those values is nothing. Not NULL, just nothing. If you look at the raw CSV log file the empty field looks like ",,".

    Right now I am trying to use REPLACE_STR to replace the empty value with a new, more friendly value for purposes of the graph chart. However it doesnt seem to work. The query returns successfully but the chart does not reflect the new, replaced value for the empty value.

    Now I know that the REPLACE_STR function works because I tested it on one of the other unique values in the field - it worked fine.

    Here is the query. Any help you can provide is much appreciated.

    SELECT Replace_STR([Field_Name],'','New_Value'),
    COUNT (*) AS [My Chart Data]
    INTO Chart.gif
    FROM 'Z:\Authentication Log File.csv'
    GROUP BY [Field_Name]

    CSV and TSV input formats

  • Re: REPLACE_STR Not Working!!!

    Mar 21, 2007 11:31 AM|Anonymous|LINK

    If your csv has empty bare fields, those are in fact nulls. You can test that by putting a "WHERE [Field_Name] IS NULL" clause in your query.
    If your fields were quoted, then they would be empty strings. You would see those by putting a "WHERE [Field_Name] = ''" clause in your query.

    If you want to replace nulls, use the COALESCE function:
    SELECT COALESCE([Field_Name],'New_Value') AS Non-Empty, COUNT(*) AS [My_Chart_Data]

    If you want to replace empty strings, you'll have to use a case statement:
    SELECT Non-Empty, COUNT(*) AS [My_Chart_Data]
    USING CASE [Field_Name] WHEN '' THEN 'New_Value' ELSE [Field_Name] END AS Non-Empty

    CSV and TSV input formats

  • Re: REPLACE_STR Not Working!!!

    Mar 21, 2007 12:01 PM|Anonymous|LINK

    Daniel,
    Thank you so much. Your suggestion worked perfectly!

    Using the COALESCE function I was able to replace the NULL entries with a customer alias and now the query is working great.

    Thanks so much!!!

    CSV and TSV input formats