Hello Community,
Although I have stated 'Transpose' in the title of this topic, 'Transpose' may not be what I'm actually trying to achieve.
In column 'Resolution Description, see image, (Install at Timezone), I'm trying to write a query that will arrange the records in the cell onto one line.
If you look at the image, you will notice the highlighted cell, has the following records:
Date: 28.0.2019 2:16:44 +00:00-
Confirmed By: Garith Cashier
Resolving Step: Checked cabling
Traces saved on: N/A
Problem code updated: Yes
I would like help writing a query that will place on the records above onto one line like the following:
Date: 28.0.2019 2:16:44 +00:00-Confirmed By: Garith Cashier Resolving Step: Checked cabling Traces saved on: N/A Problem code updated: Yesru
Below is the table structure
CREATE TABLE SFBI.dbo.DAY_REPORT (
[Callback Notes (Install at Timezone)] VARCHAR(1950) NULL
,[Resolution Description (Install at Timezone)] VARCHAR(2100) NULL
,[Information Notes (Install at Timezone)] VARCHAR(4050) NULL
,[Interaction Notes (Install at Timezone)] VARCHAR(4050) NULL
,[Information Required Notes] VARCHAR(2450) NULL
,[Resolution Summary] VARCHAR(50) NULL
,ROID VARCHAR(50) NULL
) ON [PRIMARY]
GO
Please let me know if you need any furhter information from me to help me with this query
Carlton
Any help will be greatly appreciated guys
Is it a tool tip
Which needs to be added to the column
Hi harishgg1
Thanks for reaching out.
I forgot to mention that the reason the data is stored the way I described is because the data is stored as a carriage return.
Someone suggested doing the following, but I get the code to work
SELECT REPLACE([Resolution Description (Install at Timezone)] ,char(13)+char(10),' ') Resolution Description (Install at Timezone)
FROM DAY_REPORT
I've been trying the following from the link you provided, but with no luck
select replace(theColumn, char(13) + char(10), '') from yourTable
I tried the following:
SELECT
REPLACE(DAY_REPORT.[Resolution Description (Install at Timezone)], CHAR(13) + CHAR(10), ' ') AS expr1
FROM dbo.DAY_REPORT
But still getting carriage return
Quite possible
Other characters are there
Along with
harishgg1:
Along with
Not sure what you mean here?
What I mean is example
Carriage return... some other character here
Other way could be
Extract only text number characters
From the column
Experts on this forum... please help
Apparently, the following should work, but it still gives me carriage returns:
SELECT
REPLACE(DAY_REPORT.[Resolution Description (Install at Timezone)], CHAR(13) + CHAR(10), ' ') AS Resolution
FROM dbo.DAY_REPORT
Maybe this will work
If the column data type is text then you need to cast to nvarchar first then replace SELECT REPLACE(REPLACE(cast(@str as nvarchar(max)), CHAR(13), ''), CHAR(10), '')
Carltonp
Did my last suggestion
Cast as nvarchar before replacing
Work
Just noticed your suggestion.
Going to try it right now..
Thanks
harishgg1:
@str
Hi Harishgg1, before I your suggestion. Don't I need to declare @str as string first?
Instead of @str just put your column
SELECT REPLACE(REPLACE(
cast( column as nvarchar(max)), CHAR(13), ''), CHAR(10), '')
From table
1 Like
I have intepreted your code as follows:
replace (replace (cast (dbo.DAY_REPORT.[Resolution Description (Install at Timezone)] as nvarchar(max)), char(13), "), char(10), ").
However, I'm getting invalid syntax
IT WORKED!
SELECT REPLACE(REPLACE(
cast(DAY_REPORT.[Resolution Description (Install at Timezone)] as nvarchar(max)), CHAR(13), ''), CHAR(10), '')
From dbo.DAY_REPORT
Thanks Harishgg1.. You absolute genius!