SQL Server 2012 :: How To Preserve Tags In Varchar Output
May 6, 2015
I have following XQuery:
declare @xmldoc as xml
select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>'
select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"
My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?
View 2 Replies
ADVERTISEMENT
May 20, 2008
Hi !
i have a function written in c# which removes all html tags from the provide string like
public static string RemoveHTML(string HTML)
{
return Regex.Replace(HTML, "<(.|)*?>", "");
}
how can i apply such functionality to varchar field which removes all the html tags from it in stored procedure
Regards,
DiL
View 12 Replies
View Related
May 14, 2008
I have the following code tha imports the contents of a text file into a varchar(max) field.
Unfortunately the CR/LF are stripped out when I look at the field.
How can I preserve them?
Code Snippet
DECLARE @obj VARCHAR(MAX)
SELECT @obj=BulkColumn
FROM
OPENROWSET(BULK 'C:qsiObjectCreation.sql',SINGLE_CLOB) AS ExternalFile
insert into scriptor (script) values (@obj)
View 2 Replies
View Related
May 6, 2015
I want to replace div tags with p tags in a column in sql.
<div style: bold> abc </abc>
<div> efgh></div>
required output:
<p>abc</p>
<p>efgh</p>
View 1 Replies
View Related
Apr 25, 2014
I am new to T-SQL development and here's what I am trying to do.
declare @cmd varchar(255)
set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)'
while (@cmd <=13)
begin
insert into #dbcheck values (@cmd)
Set @cmd = @cmd + 1
end
but it is giving me an error
'Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)' to data type int.'
Note - #dbcheck has only one column id int
View 3 Replies
View Related
May 7, 2014
If I have a table
CREATE TABLE [dbo].[logg](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](MAX) NULL)
insert logg (Details) values('')
insert logg (Details) values(null)
Will both statements above access only a single page (as it fits into one page) or does the VARCHAR(MAX) always put its data on a separate page. If so, is the null insert treated differently from the '' insert?
View 2 Replies
View Related
Dec 15, 2014
How do I pass a variable to an INT calculation?
CREATE PROC CLEAR_MY_TABLE
@TableStat varchar(30)
AS
If OBJECT_ID('MyDB.dbo.' + @TableStat + '') is not null
BEGIN
---PRINT 'I FOUND THE TABLE'
DECLARE @count INT = -1;
SELECT @count = (Select COUNT(*) FROM [dbo]. + @TableStat);
IF (@count > 0)
BEGIN
TRUNCATE TABLE @TABLESTAT
END
View 8 Replies
View Related
Mar 9, 2015
I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is 335:55:20.97 duration time. My code is below.
CPUTM = 335:55:20.97 duration time
CPUZIPTM = 0:00:01.96 duration time
select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) > '02:00:00.00')
View 2 Replies
View Related
Mar 19, 2015
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
View 9 Replies
View Related
Jun 12, 2015
I have such Function:
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'TwoDigitsNumber' AND type = 'FN')
DROP FUNCTION MinimumOFThree;
GO
CREATE FUNCTION TwoDigitsNumber(@a int)
RETURNS nvarchar(20)
[Code] ....
The only first letter 'f', 's', 'e' is inserted in value instead 'first', 'second', 'equal'.
Why ? How can i insert whole string
View 9 Replies
View Related
Aug 14, 2015
I have column moddat which is of varchar(10,null)
Here is my data:
20020415
20020508
19991104
19990701
20040514
20021112
20020124
19990628
20020514
20010822
I want those data in this format YYYY-MM-DD
How to convert varchar to datetime?
View 2 Replies
View Related
Dec 19, 2013
Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:
CREATE TABLE [dbo].[tblKppHierarchyPcl]
(
[ID] NUMERIC(18,0) NOT NULL,
[Name] VARCHAR(500),
[PartStructureKey] NUMERIC(18,0) NOT NULL,
[PartNumber] VARCHAR(500) NOT NULL,
[ParentPartNumber] VARCHAR(500) NULL,
[code]...
Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.
View 8 Replies
View Related
Mar 11, 2014
i have a situation where is need to get email address from a varchar column. Here is some sample data for five records; The format can be change.
dummy text;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk
Admissions dummy text;
T: +44 (0)1234 4444;
E: xyz@co.uk;
dummy text;
dummy text;
Tel: +123 32323 33;
Email: test@yahoo.com;
dummy text;
t: +88 (0) 115 333 5553;
f: +99 (0) 115 222 8888
e: dummy@gmail.com;
dummy text;
t: +11 (0) 222 222 2222;
e: myemail@test.com;
w:http://www.yahoo.com/faqs;
View 4 Replies
View Related
Jan 31, 2014
I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
View 3 Replies
View Related
Feb 26, 2014
I know that if I have an nvarchar column I can use an equality like = N'supersqlstring' so it doesn't implicit cast as a varchar, like if I were to do ='supersqlstring'. And then I'll be a big SQL hero and all my stored procedures will run before a millisecond can whisper.
But if I'm comparing an nvarchar column to a varchar column, is it better to cast the varchar 'up' to an nvarchar or cast the nvarchar 'down' to a varchar?
For instance:
cast(a.varchar as nvarchar(100)) = an.nvarchar
or
cast(an.nvarchar as varchar(100)) = a.varchar
Leaving aside non-matching, like (at least I don't think) that SQL considers the varchar n to be equal to the nvarchar ń, what's the best way to handle this?
Pretend for a moment that each column contains a mixed letter and number ID with no accented or wiggly-squiggly Unicode characters; it's just designs clashing.
Is there a performance hitch doing it one way or another? Should I use COLLATE? Should one of the columns be altered?
View 8 Replies
View Related
Mar 11, 2014
I have the following code in a SP. The 2 if statements for the @@ Error I added only for testing as I suddenly started get the following error at the 1st @@Error statement
Msg 232, Level 16, State 2, Line
Arithmetic overflow error for type varchar, value = 10000.00000.
I cannot have this SP running minutes it must be done in seconds.
I have tried the folloiwng changes but then it takes to long.
-- set @new = 'x' + right('0000' + convert(varchar(5), round(rand() * 10000, 0)), 4)
-- set @new = 'x' + right('0000' + convert(varchar(4), right(round(rand() * 10000, 0),4)), 4)
declare @newvarchar(5),
@xstsint,
@new_retvarchar(5)
,@Cnt as int /* just for my testing */
set @cnt = 0 /* just for my testing */
[Code] ......
View 6 Replies
View Related
Oct 17, 2014
I keep getting requests to increase the width of a varchar colum every now and then.
I want to ask if its perfectly ok when you have active users connecting to the application to do this?
View 7 Replies
View Related
Jul 8, 2015
I have a table with column "Data" as VARCHAR, with entries like below.
1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68
I want to select the data by converting varchar to int for numeric values and for alphanumeric it should display as it is.
SELECT CAST(dataAS INT) FROM record_tab
getting below error
Conversion failed when converting the varchar value 'A1'
View 9 Replies
View Related
Jul 29, 2015
any tricks to separate this column from:
"testlname,testfname comment section > comment section testlname,testfname 6/30/2015 9:05:58 AM > testlname,testfname 6/30/2015 9:06:18 AM > comment section testlname,testfname 6/30/2015 9:10:05 AM > comment section"
to:
col1 Col2 col3
testlname,testfname 6/30/2015 9:05:58 AM comment section
testlname,testfname 6/30/2015 9:06:18 AM comment section
testlname,testfname 6/30/2015 9:10:05 AM comment section
View 1 Replies
View Related
Oct 28, 2011
I have a table with a column that has html text. The column with html text is pretty big datatye varchar(max)... I wanted to check if any of you have any function that I can use to Strip out the HTML tags... I saw couple of version online, but it was running too slow..
This is the one I used: [URL] .....
View 9 Replies
View Related
Aug 12, 2014
I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.
I am also including example of how the account_numbers are formatted.
R222209
R222220
R222222
R222212
R221123
F707768
[Code] .....
View 5 Replies
View Related
May 4, 2015
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)
[Code] .....
View 6 Replies
View Related
Aug 6, 2015
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue
View 4 Replies
View Related
Feb 28, 2014
I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.
, CASE
WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))
END AS 'System Verified'
View 3 Replies
View Related
Oct 7, 2014
I have a sql query that gets the count of exams held in each month.
Below is the code that I have used.
select Examid, count(*) as CumCount
from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate())
and Year(EXOCCRDATE) = Year(getdate()) group by Examid
The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when there is no value returned in the output, i want a default value shown in the output.
I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So what I need is I need a text like 'No Data' to be shown when no value is returned by the query.
I tried using case but it does not work.
View 6 Replies
View Related
May 3, 2006
Hi,
I am dumping varchar(40) column from a OLE DB Source to Excel file but it is failing due this error
[Excel Destination [239]] Error: Column "ItemDescription" cannot convert between unicode and non-unicode string data types.
How can I resolve this issue?
Thanks
View 8 Replies
View Related
Jul 31, 2015
The below query will read the data in XML format but any query to read from each column of XML tags easily?
SELECT CAST(record AS XML), record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
View 5 Replies
View Related
Apr 14, 2015
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
View 3 Replies
View Related
Aug 14, 2015
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATEtbl
SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000)
FROM Table tbl
JOIN Person p ON p.PersonID = tbl.PersonID
JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2
WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%
'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.
View 5 Replies
View Related
Nov 19, 2013
I need a query which results the output as comma separated
for example ,
select name from tablename;
say for example this produces the following,
Name
Jack
John
Smith
I need the follwoing,
Jack,John,Smith
View 2 Replies
View Related
Jan 22, 2014
Assume i have 3 tables
Person(personname,age)
Children(childname,personname)
car(carname,personname)
A persone can have multiple cars
A person can have multiple children
Its not possible to display the results in SQL rows and columns.
If i try to it will give
PersonName Carname Childname
Sachin Audi C1
Sachin Maruti C1
Sachin Audi C2
Sachin Maruti C2
Instead of writing seperate queries the application wants to receive an xml output as follows
<person>
<pname>sachin</pname>
<car>audi</car>
<car>bmw</car>
<cname>c1</cname>
<cname>c2</cname>
<person>
How to get this output ?
View 1 Replies
View Related
Jan 29, 2014
Below is the sample data
create table #sample
(name varchar(100),
id int)
insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')
I need output as attached ...
View 5 Replies
View Related
Aug 13, 2014
I've the following T-SQL block that I'm executing where I want to print the following output in next line but not sure how to do this:
This is printing everything in one line:
SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
How do I do this:
SET @BODYTEXT = ' Dear ' + @fname +
',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
Also how can I create a table in this variable, something like this:
(TABLE) LIST THE COURSE CODE, COURSE NAME , EMPLOYEE ID, EMPLOYEE NAME
(Course Name) (Last Completed) (Now due in Month/year)
My T-SQL code:
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
,@fname varchar(100)
[Code] ......
View 2 Replies
View Related
