April 2, 2020 at 2:04 pm
I have some data that i have no control over (third party supplying the data). I am extracting it to a CSV but the end user does not want "Null"s they want blank spaces is there a way to do a mass replace on all 36 fields. Case when isnull.... = ' ' end
April 2, 2020 at 3:05 pm
If this is a manual process of extracting the data to CSV, you could probably do a find-and-replace in the CSV.
If you are using a tool (SSRS for example), you could change your SELECT <column name> to SELECT ISNULL(<column name>,'') and repeat for all columns. With it being 36 fields, you would need to do "ISNULL(" on all of them but if your query is formatted like:
SELECT <column1>,
<column2>,
and so on (ie everything is lined up neatly), you can use the "ALT+SHIFT" trick to modify multiple rows at once. ALT+SHIFT then hit down a bunch of times until you have selected all of the rows you want and type in "ISNULL(" then repeat at the end before the comma with ")" and you are done. Now if it is all on one line OR is a "SELECT *" then you will need to break that out.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 2, 2020 at 3:28 pm
I have some data that i have no control over (third party supplying the data). I am extracting it to a CSV but the end user does not want "Null"s they want blank spaces is there a way to do a mass replace on all 36 fields. Case when isnull.... = ' ' end
Where does the original data live when you try to extract it to a CSV? In a table or ???
What are you using to extract the data to CSV?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2020 at 3:54 pm
It resides in a table, I really done want to do isnull() on all fields but if i have to?
April 2, 2020 at 4:26 pm
If you have integers or other number types you might need to convert them to varchar first or they will get converted to zero.
April 2, 2020 at 4:36 pm
Yep done the conversion (10) fields converted
April 2, 2020 at 4:51 pm
It resides in a table, I really done want to do isnull() on all fields but if i have to?
The reason why I asked what you're doing the conversion to CSV with is because some tools will just make an empty string between delimiters for NULLs. IIRC, BCP will do that auto-magically for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2020 at 6:36 pm
Lee Hopkins wrote:It resides in a table, I really done want to do isnull() on all fields but if i have to?
The reason why I asked what you're doing the conversion to CSV with is because some tools will just make an empty string between delimiters for NULLs. IIRC, BCP will do that auto-magically for you.
So will SSIS and Powershell...
It sounds like the OP is using copy/paste from the grid - which converts the NULL values to the word NULL. If that is the case - then pretty much any tool where the data is pasted has Ctrl-H available to replace NULL with blank.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 6, 2020 at 5:05 pm
I wrote a quick dynamic query that will do this. Just input the database name, schema, and table name and it will select all from the table with any NULL values replaced with an empty string.
Sample table:
DROP TABLE IF EXISTS #table ;
CREATE TABLE #table
(
ID INT IDENTITY,
Name VARCHAR(50),
Date DATE
) ;
INSERT INTO #table (Name, Date) VALUES
('A', '2020-01-01'),
('B', NULL),
(NULL, '2014-06-28') ;
Dynamic query:
------------Declare/set variables
DECLARE @DatabaseName NVARCHAR(150),
@SchemaName NVARCHAR(25),
@TableName NVARCHAR(150),
----Variables to hold/populate SQL statements
@ColNull NVARCHAR(MAX), --wraps column names in ISNULL statement
@SQLTemp NVARCHAR(MAX), --initial SQL to populate temp table with column info
@SQL NVARCHAR(MAX), --final SQL to select all and replace NULL values with empty string
----Strings to simplify population of SQL
@SingleQuote CHAR(1) = '''',
@NullString1 NVARCHAR(25) = 'ISNULL(TRY_CAST(',
@NullString2 NVARCHAR(50) = ' AS NVARCHAR(MAX)),'+ '''' + '''' + ') AS ' ;
SET @DatabaseName = 'tempdb' ;
SET @SchemaName = 'dbo' ;
SET @TableName = '#table' ;
SET @ColNull = N'' ;
--------------Set @SQLTemp to get table column info
SELECT @SQLTemp = N'
SELECT c.column_id AS ColumnID,
c.name AS ColumnName
FROM ' + @DatabaseName + '.sys.columns c
WHERE c.object_id = OBJECT_ID(N' + @SingleQuote + @DatabaseName + '.' + @SchemaName + '.' + @TableName + @SingleQuote + ')
ORDER BY c.column_id' ;
-- SELECT @SQLTemp ;
------------Create #table_columns and populate by executing @SQLTemp
DROP TABLE IF EXISTS #table_columns ;
CREATE TABLE #table_columns
(
ColumnId INT,
ColumnName NVARCHAR(150)
) ;
INSERT INTO #table_columns EXEC (@SQLTemp) ;
------------Populate @ColNull with [ NULLIF(COLUMN) AS COLUMN ] for every column to use in SQL below
SELECT @ColNull += N', ' + ColNull
FROM
(
SELECT @NullString1 + c.ColumnName + @NullString2 + c.ColumnName AS ColNull
FROM #table_columns c
) x;
-- SELECT @ColNull ;
------------Final @SQL statement to replace all NULL values with empty string
SET @SQL = N'
SELECT ' + STUFF(@ColNull,1,1,'') + '
FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName ;
-- SELECT @SQL ;
------------Execute/end
--PRINT @SQL ;
EXEC sp_executesql @SQL ;
Edit: Caveat that I'm using TRY_CAST(... AS NVARCHAR(MAX)) in ISNULL, but if the TRY_CAST failed and resulted in a NULL, it would replace that column with an empty string as well. I can't think of why anything COULDN'T be converted to NVARCHAR(MAX), but I suppose it's possible, so that's something to be aware of.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply