Remove Spaces in a field
I need to remove the spaces in the field called (qryAllocText) this field is basically an email that been copied into this field. When we try send the data using reporting services in brings up errors in converting the data into excel format.
Please can you advise what i need to do in the sql script to remove spaces and line breaks etc
Please can you advise what i need to do in the sql script to remove spaces and line breaks etc
Select ctName,
C.catName,
Q.qryNo,
actQryNo,
qryAccNo,
qryNasRefNo,
qryAllocDept,
Department_name,
qryLogDept,
qryAllocText,
qryFinalText,
qrySource,
qryMethod,
qryStatus,
E.Surname + ' '+ E.First_Name as Employee,actUser,
Q.qryLogTime,
A.actTime,
actKey
From CTS..Queries Q
inner join (select Max(actKey) as MaxKey
,actQryNo as MaxactQryNo
from Actions
group by actQryNo) as MaxAction on MaxAction.MaxactQryNo = Q.QryNo
inner join Actions A on MaxAction.MaxKey = A.actKey
inner Join CTS..CallType_Category_Links L on L.ccID = Q.qryCallTypeCategory
inner Join CTS..Categories C on C.catID = L.Category
inner Join CTS..CallTypes T on T.ctKey = L.CallType
inner Join CTS..Customers Cus on Cus.cstAccNo = Q.qryAccNo
Inner Join SYS04_Frontier..Frontier_sys04_Employees E on E.Logon_Name = Q.qryAllocUser
Inner join SYS04_Frontier..Frontier_sys04_DEPARTMENTS D on D.ID = Q.qryAllocDept
Where ctName in('Branch Banking Existing Business', 'Branch Banking New Business')
and Q.qryStatus IN('Pending','Unresolved')
Order by actQryNo
ASKER
Hi RiteshShar
Thanks for the assistance
When I try run the new script i get the following error
Msg 8116, Level 16, State 2, Line 3
Argument data type ntext is invalid for argument 1 of ltrim function.
Thanks for the assistance
When I try run the new script i get the following error
Msg 8116, Level 16, State 2, Line 3
Argument data type ntext is invalid for argument 1 of ltrim function.
which SQL Server version are you using? it is working fine in my SQL Server 2005.
sorry, ignore my previous post
since you are using nText, you can't use rtrim and ltrim, below link will help you.
http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html
http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html
or you can alter your ntext column to nvarchar(max) in sql server 2005 to get maximum benifit of new data type. if you alter column, my first script will work.
you can do something like this also.
Select ctName,
C.catName,
Q.qryNo,
actQryNo,
qryAccNo,
qryNasRefNo,
qryAllocDept,
Department_name,
qryLogDept,
rtrim(ltrim(convert(nvarchar(max),qryAllocText))),
qryFinalText,
qrySource,
qryMethod,
qryStatus,
E.Surname + ' '+ E.First_Name as Employee,actUser,
Q.qryLogTime,
A.actTime,
actKey
From CTS..Queries Q
inner join (select Max(actKey) as MaxKey
,actQryNo as MaxactQryNo
from Actions
group by actQryNo) as MaxAction on MaxAction.MaxactQryNo = Q.QryNo
inner join Actions A on MaxAction.MaxKey = A.actKey
inner Join CTS..CallType_Category_Links L on L.ccID = Q.qryCallTypeCategory
inner Join CTS..Categories C on C.catID = L.Category
inner Join CTS..CallTypes T on T.ctKey = L.CallType
inner Join CTS..Customers Cus on Cus.cstAccNo = Q.qryAccNo
Inner Join SYS04_Frontier..Frontier_sys04_Employees E on E.Logon_Name = Q.qryAllocUser
Inner join SYS04_Frontier..Frontier_sys04_DEPARTMENTS D on D.ID = Q.qryAllocDept
Where ctName in('Branch Banking Existing Business', 'Branch Banking New Business')
and Q.qryStatus IN('Pending','Unresolved')
Order by actQryNo
ASKER
Sorry to bug you but its now come up with the following error
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'max'.
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'as'.
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'max'.
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'as'.
which sql server version are you using?
if you are using sql server 2000 than this should work, I don't have sql server 2000 to check with.
Select ctName,
C.catName,
Q.qryNo,
actQryNo,
qryAccNo,
qryNasRefNo,
qryAllocDept,
Department_name,
qryLogDept,
rtrim(ltrim(convert(nvarchar(5000),qryAllocText))) as 'qryAllocText',
qryFinalText,
qrySource,
qryMethod,
qryStatus,
E.Surname + ' '+ E.First_Name as Employee,actUser,
Q.qryLogTime,
A.actTime,
actKey
From CTS..Queries Q
inner join (select Max(actKey) as MaxKey
,actQryNo as MaxactQryNo
from Actions
group by actQryNo) as MaxAction on MaxAction.MaxactQryNo = Q.QryNo
inner join Actions A on MaxAction.MaxKey = A.actKey
inner Join CTS..CallType_Category_Links L on L.ccID = Q.qryCallTypeCategory
inner Join CTS..Categories C on C.catID = L.Category
inner Join CTS..CallTypes T on T.ctKey = L.CallType
inner Join CTS..Customers Cus on Cus.cstAccNo = Q.qryAccNo
Inner Join SYS04_Frontier..Frontier_sys04_Employees E on E.Logon_Name = Q.qryAllocUser
Inner join SYS04_Frontier..Frontier_sys04_DEPARTMENTS D on D.ID = Q.qryAllocDept
Where ctName in('Branch Banking Existing Business', 'Branch Banking New Business')
and Q.qryStatus IN('Pending','Unresolved')
Order by actQryNoASKER
we using sql server 2000
what about my last query?
ASKER
this seems to be working fine will check how the data come out
Thanks
Thanks
no problem. enjoy!!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window