HOWTO remove whitespace from an automation string to export to a SQLPlus SELECT command LockedOut.sql file

Question

This is an automation of a command to SQLPlus 12c on Linux from Windows 18_3 version on PowerShell 5.1 with Microsoft modules loaded. I need to clean out the whitespace of the string to input wildcard data on an automation Select script (the final script will find a missing TIFF image and reinsert it). I am UNABLE to remove the white space before the tee.

The latest attempts are in the post but I have tried Trim, Split, Replace, Remove, Substring, >>, Write-Host -NoNewline,... I am SO close.

When I Write-Host -NoNewline I succeeded in removing the CRLF but not so as I can Tee, Write-Out, or Out-File the content that way.

#Add-Type -AssemblyName System.Data.OracleClient
$filefolder = "C:\EMSCadre\iGateway\clint\Input_Images\"
$Files = Get-ChildItem $FileFolder -Name -File
$longname = $Files.Get(2)
$shortname = $longname.Replace("_tiff","").Replace("cns","").Substring(9).Split('".tif"')
echo "select LD_CASE_NUMBER FROM LOG_data where ld_message_3 like %$shortname%" |
    tee -Verbose c:\scripts\input\lockedout_test.sql
type c:\scripts\input\lockedout_test.sql

#Failed attempts
#echo "select LD_CASE_NUMBER FROM LOG_data where ld_message_3 like %($shortname1.TrimEnd('_',"")%" |
#  tee -Verbose c:\scripts\input\lockedout_test.sql

Latest Results showing Whitespaces before last %:

select LD_CASE_NUMBER FROM LOG_data where ld_message_3 like %100838953_180130001    %
select LD_CASE_NUMBER FROM LOG_data where ld_message_3 like %100838953_180130001    %

Details to help troubleshoot:

PS C:\scripts> $Files
2823910000.tif
2823910002.tif
cns20180827_100838953_180130001_tiff.tif
exposureworks-dynamic-range-test-f16-graded-TIFF-RGB-parade.jpg

PS C:\scripts> $shortname
100838953_180130001

I think your main problem is the .split() method which will take every single char and split seperately on it. You should show the names before and how they should look like after cleaning. Deciphering from your non working code what you intend is cumbersome. Please Read what a [mcve] is. — , Jan 11 '19 at 23:23
It is clear but let me explain again: It takes a file, trims THE NAME down, and then adds it to a select script I will then use "select LD_CASE_NUMBER FROM LOG_data where ld_message_3 like %100838953_180130001 %" I have it nearly there. I'm just trying to get the last whitespace out and export to the sql file as a Select script for SQLPlus. — Patrick Burwell, Jan 11 '19 at 23:50

score 1 · Accepted Answer · answered Jan 12 '19 at 12:44

Looks to me like the last step (Split()) of the statement

$longname.Replace("_tiff","").Replace("cns","").Substring(9).Split('".tif"')

is supposed to remove the extension from the file name. That is not how Split() works. The method interprets the string ".tif" as a character array and splits the given string at any of those characters (", ., f, i, t). Splitting the string 100838953_180130001.tif that way gives you an array with 5 elements, the last 4 of which are empty strings:

[ '100838953_180130001', '', '', '', '' ]

Putting the variable with that array into a string mangles the array into a string by concatenating its elements using the output field separator ($OFS), which by default is a single space, thus producing the trailing spaces you observed.

To remove the prefix cns..._ and the substring _tiff as well as the extension .tif from the file name use the following:

$shortname = $longname -replace '^cns\d*_|_tiff|\.tif$'

That regular expression replacement will remove the substring "cns" followed by any number of digits and an underscore from the beginning of a string (^), the substring "_tiff" from anywhere in a string, and the substring ".tif" from the end of a string ($).

HOWTO remove whitespace from an automation string to export to a SQLPlus SELECT command LockedOut.sql file

1 Answers1