One purchased app stored user's multiple lines input into a varchar column in which including char(13) and char(10).
My app need to select this value and format to multiple lines text in one text box.
How to code to output it?
Impact on performance of changing Text column size.
However, many of them were regarding older releases of SQL Server.
Does the architecture in 2012, 2014 releases make this less of an issue ?
In other words, why bother going from Varchar(1000) to Varchar(50) ?
I'm just thinking maybe there is column compression automatically now.
How to convert MM/YYYY to MM/DD/YYYY and also YYYY to 01/01/YYYY.
This column is text column and has many other inputs.
With this clause "select convert(varchar(16), getdate(), 101)" I can get mmddyyyy but
How can I get time such as mmddyyyyhhmm
Thanks
Daniel
create table tblxmldata
(id int, xmltext varchar(max))
insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText>')
insert into tblxmldata values(1,'<associatedText><value type="PO">Check sugar today please</value></associatedText>')
I want the output as
GTT taken
Check sugar today
When I change a column from text to varchar using thedesign view of a table within Enterprise Manager thevarchar value (less than 8000 characters) appears in thecolumn but does SQL Server automatically delete the textvalues from their pages?If not are they removed by routine reindex/defrag orshould I create a new table, import from the text asvarchar and drop the old table to make sure the pagesstoring the original text version of the values aredeleted?..
View 1 Replies View RelatedA column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
Cannot use dynamic sql in current context. So need some help regarding this.I am developing a stored procedure to update a table. Sending Column names as parameters, but not able to use them as given below.INSERT INTO Books (@Column1, @Column2) values.. Any way to execute without using dynamic sql?..Thanx.
View 1 Replies View RelatedI need to extract specific text elements from a varchar column. There are three keywords in any given string: "wfTask," "wfStatus" and "displayReportFromWorkflow." "wfTask" and "wfStatus" can appear multiple times, but always as a pair and will each be followed by by "==" (with or without surrounding spaces). "displayReportFromWorkflow" is always followed by "(" and there can be spaces on either side. The text elements will be between a pair of double quotes, and following one of keywords. For each row, I need to return the task, status and report name.
declare @t table (rowID int, textValue varchar(1024))
insert @t
(rowID, textValue)
values
[Code] ....
Output:
rowID, Task, Status, ReportName
----- --------- ------- ------------------------
1, Issuance, Issued, General Permit
2, Issuance, Issued, Capacity Letter Type III
2, Review, Denied, Capacity Letter Type III
I started with a string splitter using the double quote character, referencing elements "i" and "i+1" where the text like '%wfTask%' or '%wfStatus%' or '%displayReportFromWorkflow%', but the case of multiple task/status in a row has confounded me so far.
Unfortunately, CLR is not an option.
I am unable to get FTS working where the column to be searched is type varchar(MAX) or Text. I can get this to work if my column to be indexed is some statically assigned array size such as varchar(1000).
For instance this works, and will return all applicable results.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](1000) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);
And this does not. It returns zero results what so ever.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](MAX) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);
Could someone please tell me what I need to do to enable FTS on varchar(MAX) or Text columns?
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;
I have a table with number and varchar columns. The last insert statement has 1 inserted.
The  select statement should retrieve   Â
a         b                                                         Â
1Â Â Â Â Â Â Â Â Â 1
CREATE TABLE [dbo].[test1](
 [a] [int] NULL,
 [b] [varchar](10) NULL
) ON [PRIMARY]
 insert into test1 values (1,'a')
 insert into test1 values (2,'b')
 insert into test1 values (4,'d')
 insert into test1 values (12,'x')
 insert into test1 values (15,NULL)
 insert into test1 values (1,1)
Working on partitioning a few large tables. One of the tables included a text column and the €œTEXTIMAGE_ON [PRIMARY]€? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20)
When I attempted to run the ALTER TABLE SWITCH I encountered the error
Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'.
After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has €œlob_data_space_id€? with a value of 1 for this table.
I created a copy of the table with the text column altered to varchar and one with just the varchar to begin with. After copying data from the original table, I tried to run the alter switch. It failed once again for the text column altered to varchar table, but it worked for the varchar from the start.
Since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.
BOL States:
The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.
Thanks
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?
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'
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
I am trying to find a solution to get the result set to fetch a particular string format from a table in my database, which has a column of NVARCHAR data type
CREATE TABLE #ActivityComments(Comments NVARCHAR(MAX))
INSERT INTO #ActivityComments VALUES('This is the study code for Field Phase S14-04932-01')
INSERT INTO #ActivityComments VALUES('Phase reporting has the study S15-04931-01 which is obselete')
INSERT INTO #ActivityComments VALUES('Phase running study code S14-04932-02 is not valid')
The output of the query should be like:
S14-04932-01
S15-04931-01
S14-04932-02
Is there any way possible to achieve this..
I am using SQL2005 and ASP.NET 2.0
I have one column in database called DateTime and it is defined like type datetime.It is formated like: day.month.year hour:minutes:seconds
My question is: I want to get date from Column DateTime in format day.month.year in SELECT query, not in stored procedure.
thanks
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
I am currently working for the Sybase to Sql Server migration project and have been able to test migrate few tables using SSIS. After migration, i was doing some data comparision and could see the date formats are different between Sybase and Sql server. However, there are no issues with data like day in sybase becoming month in Sql server except the formats are different.
Do I need to act on this date formats? Not sure if this would cause any issues in front end application that will consume sql server date data.
I have an xml stored in a coulum of a table and I use the following query to extract an xml element :
select CONVERT(XML,CONVERT(NVARCHAR(max),Response)).value('(/Quote/error)[1]','nvarchar(max)') as Excepiton .The result of the expression is :
TL43:The product has no marked price.;I would like to select only the code : TL43and then separately I would like to select The product has no marked price.Is there a way I can do it?
I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:
Select CustomerID, customername,
from CustomerTable
where CONTAINS ((CustomerID), '"430*")
the query result only return all customer id which has space in front 430 or start with 430
ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"
but did not return customerID which doesn't has space in front of it
Ex: x430xxx
If I use LIKE '%430%', the result return are both have space in front and don't have space in front.
Until SSMS 2012 I copy and paste grid view with header directly to Excel. Problem starts when I try to copy results from SSMS 2012 because text column copy with some format and spreads around sheet and not in only one cell. What do I have to do how to select text columns and how to copy them to excel. IN SSMS 2008 it all works OK.
View 3 Replies View RelatedHello. I using a simply SELECT statement to retrieve some data from aSQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXTbecause the amount of data. Anyway, my SQL statements worked just finewhen I was using VARCHAR, but now since I am using TEXT, I am onlyreceiving part of the content back. Do I have to do some sort ofspecial Casting or something if I want to get all the content back?It's over 8,000 characters. Thank you very much. I have racking mybrain on this for a while.
View 8 Replies View RelatedHi All,We're running SQL Server 2000, SP3.I have a stored procedure that consists of a single Select statement.It selects a bunch of columns one of which is a column of data typeTEXT.SP takes 30 sec to run which causes timeouts on the Front End.When I comment out the Text column from the select it only takes 1Sec.Is there anything I can do about it? I know I can't index a Textcolumn. It's also not used in the where clause, so no need forFull-Text Search.But we absolutely have to have it in the Select clause.Thanks for the help in advance.~Narine
View 5 Replies View Relatedhow to import a text file with a list of NI numbers into a new table with a column to list all the NI numbers? I think I use the Select INTO clause, but not sure how to do this?
View 1 Replies View RelatedI have a column A as 'text' datatype. This has CR-LF in between data.How to remove it.
I am trying to copy result from SQL to Excel
Eg:
A CR LF
ABC CR LF
DVB CR LF
The output should be:
A
ABC DVB
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
Hello,
I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
I have a table like below
create table #temp
(
valid_id int not null,
valid_no varchar(50)
)
10001
20002
30011
40012
i need to select the valid no without '0' lie 1,2,11,12, how do i?
I am trying to select leveling; I have table like this;
FACC_WID FACC_BKEY FACC_CODE FACC_DESC FACC_CODE_DESC FACC_DESC_CODE FACC_H1_L5_CODE FACC_H1_L5_DESC FACC_H1_L5_CODE_DESC FACC_H1_L5_DESC_CODE FACC_H1_L4_CODE FACC_H1_L4_DESC FACC_H1_L4_CODE_DESC FACC_H1_L4_DESC_CODE FACC_H1_L3_CODE
[Code] ...
Now I need to make 5 levels like this:
1
10
100
1001
100100
When column FACC_CODE have 1 char then its a level one so I put this in FACC_H1_L1_CODE..
I this possible in (SSIS)?
How can I do this in TSQL, there are alot of wild chars but I could not find how to count them, in case below I only want id =1 and 2
this sql is not correct yet...
DECLARE
@a VARCHAR(22) = ' alpha1234 qwerew', @b VARCHAR(22) = '3456_Bravo', @c VARCHAR(22) = ' only_three123_Delta'
--SELECT 1 id, @a c UNION select 2 id, @b c UNION SELECT 3 id, @c c
SELECT * FROM
(SELECT 1 id, @a c UNION select 2 id, @b c UNION SELECT 3 id, @c c) b
WHERE c LIKE '%[1_3]%'
I rand code below but got an error said
ORDER BY items must appear in the select list if SELECT DISTINCT is specified
select distinct ORDERID from ORDER where ORDERID = '123456' order by ORDERDATE desc // error
I have to modify code to
select distinct ORDERID,ORDERDate from ORDER where ORDERID = '123456' order by ORDERDATE desc
But, I really need only "ORDERID". How to make "ORDERDATE" not displaying?