August 7, 2014 at 9:45 am
I have an excel file load to a staging table using powershell, the load is completed, but facing challenges on remove non printable characters.
I have multiple decimal(16,2) fields stored in a VARCHAR(255) and need to have the NON_PRINTABLE CHARACTERS removed.
Need some help...
I used the following, but it truncated anything after the decimal.
update dbo.STG_table
SET
[Fee_amt] = left([Fee_amt], case patindex('%[^0-9]%', [Fee_amt]) when 0 then [Fee_amt] else patindex('%[^0-9]%', [Fee_amt]) - 1 end)
ORIGINAL: 1368.23
after the above SQL...it returned 1368
the goal is after all the clean up of non-printable character, I can move the data to a normalized table structures with correct data type instead of VARCHAR(255)...I also tried the CONVERT(DECIMAL(16,2),FEE_AMT), but it gave me error on converting. So I believe thats caused by the non-printable character in the excel file to start with.
Need help.
August 7, 2014 at 10:32 am
I think it is easier to do this in PowerShell than T-SQL so I'd put it in the load. I don't have the code in front of me to do it, but since PoSH has the full power of .NET it shouldn't be hard to find an example.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2014 at 12:47 pm
As far as a SQL approach, here is something we use that you might find helpfull.
1. You'll need a Tally table (we call it dbo.numbers ) ** search this site for Tally Table by Jeff Moden
2. Create a table for ascii characters and their replacements (we call it dbo.characterReplacementMap) ** code below. And use the attached script to populate it.
3. Create the attached function.
The way this works is by checking the ascii code of each character and replacing with the specified columns ascii value. (we use 32 or SPACE).
CREATE TABLE [dbo].[characterReplacementMap](
[sourceASCII] [int] IDENTITY(1,1) NOT NULL,
[description] [varchar](255) NULL,
[alpha] [int] NULL,
[alphanumeric] [int] NULL,
[numeral] [int] NULL,
[azAZ09HyphenApostrophe] [int] NULL,
[strictalphanumeric] [tinyint] NOT NULL,
CONSTRAINT [PK_characterReplacementMap] PRIMARY KEY CLUSTERED
(
[sourceASCII] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply