Query Result To Excel with Messed Up Rows (SSMS 2012)

When you export your query result from SSMS 2012 (not SSMS 2008) to a text file or copy and paste the result (with header), you may run into problem with columns (such as varcahr(max)…) that include Tab — char(9), Line feed — char(10) or Carriage return — char(13). You have garbled rows due to these invisible chars.

A quick fix is to remove them by using nested REPLACE functions in your SELECT query.
Here is and example:

--Sample code
--

SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),''), CHAR(13),'') ,CHAR(9),'')  as yourcolumn

--
 

SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),”), CHAR(13),”) ,CHAR(9),”) as yourcolumn

If you want to remove them forever, you can use an UPDATE command to update these columns in your source table.

Convert String Type British Datetime Data (DD/MM/YYYY hh:mm:ss ) in Varchar/NVarchar to Standard Datetime with T-SQL

create table test (strdt varchar(255))
--British datetime in string
insert into test values 
('13/12/2013 13:33:00 +00:00')
,('01/01/2013 13:33:00 +00:00')
,('28/02/2013 13:33:00 +00:00')
,('31/10/2013 13:33:00 +00:00')
Select 
--string manipulation and cast to datetime
Cast(substring(strdt,7,4)+substring(strdt,4,2)+substring(strdt,1,2) +' '+ substring(strdt,12,8) as datetime),

--Convert string to British datetime first and then convert the British datetime to standard
  convert(datetime, convert(datetime, Left(strdt,20), 103),112)
   
FROM test

--Or
Select   CONVERT(datetime2(3),strdt, 103) AS DateValue
,try_convert(datetime2(3),strdt,103 )
,Try_PARSE(strdt as datetime2(3) using 'en-GB')

FROM test


--clean up
drop table test

 


 

Strip non-numeric character in a column

create table test (t_id int primary key, col varchar(50))
INSERT INTO test
VALUES      (1,'462'),
            (2,'E08R'),
            (3,'E07'),
            (4,'E09'),
            (5,'E06'),
            (6,'360'),
            (7,'E04'),
            (8,'E11R'),
            (9,'E02R') 

 

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 
processTable as (
    select col,Cast(col1 as int) col1   from test
        cross apply (
            select (select C + ''
            from (select n, substring(Col, n, 1) C 
			FROM Num1  where n<=datalength(Col)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)

		)

SELECT col,col1 FROM processTable
 
 --http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters


--Another option
;with
mycte1 as
(select  col, REPLACE(col,SUBSTRING(col,PATINDEX('%[^0-9]%',col),1),'') col1 from test )
,mycte2 as (
select col, REPLACE(col1,SUBSTRING(col1,PATINDEX('%[^0-9]%',col1),1),'') col2 from mycte1)
,mycte3 as (
select col, REPLACE(col2,SUBSTRING(col2,PATINDEX('%[^0-9]%',col2),1),'') col3 from mycte2)
,mycte4 as (
select col, REPLACE(col3,SUBSTRING(col3,PATINDEX('%[^0-9]%',col3),1),'') col4 from mycte3)

Select col, Cast(col4 as int) from mycte4

drop table test

--Another example
create table test (COLUMN_A varchar(100))
INSERT INTO test VALUES
('AAAA AAAAAAA CO A.A. # 4030'),      	
('BBBBBB BBB BBB CO. #  4260.'),  	
('CCC CCCCC #3001'),
('CCC CCCCC 3001')

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 

processTable as (

select Substring(COLUMN_A, 0,Charindex('#',  COLUMN_A)) COLUMN_A , Cast(col1 as int) COLUMN_B   from test
        cross apply (
            select (select C + ''
            from (select n, substring(COLUMN_A, n, 1) C 
            FROM Num1  where n<=datalength(COLUMN_A)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)
 
        )

 Select * from processTable

 Drop table test