Hi,
I have erronous white space at the end of my 'description' field within my
'product' table. I tried using the RTRIM function but it won't let me
because it is a TEXTBLOB (text) field.
Can anyone show me how to write a query that will update all my rows
automatically?
I'm using SQL Server 2000.
Thanks!
Hello!
I am trying to run a query on an SQL-server database:
SELECT (Left(Comments, 20)) As shortComment FROM myTable
The Field Comments is a Text field (datatype is text), and I can't get the LEFT function to work with this datatype...
How can I bypass this problem? Can I convert the commentfield to varchar in the QUERY?
noccy
Hi
I sent a long string of ID from front end to my stored procedure...till now I was using varchar(8000)...but if the string crossess that limit it is breaking.
If I try to use text datatype..It doesn't support rtrim, stuff functions etc...
So could any one suggest me a best way to save a long string without any restriction of size...
My front end is C#.Net and Back End is SQL SERVER 2000
Thanks in advance
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies View RelatedHi,
Im a programmer for an university webportal which uses php and msssql.
When an user creates a new entry and his text is too long the entry is cut short and weird characters appear at the end of the entry.
For example:
http://www.ttz.uni-magdeburg.de/scripts/test-messedb/php/index.php?option=show_presse&funktion=presse_show_mitteilung&id=333
How can I set the text limit to unlimited?
Could it be something else?
Is there a way of splitting an entry to several text fields automatically?
Thanks in advance for any help you can give me,
Chris
In SQL 2005 if i was trying to insert some data with a text qualifier inside a text qualified field, it would work, for example:
"Name","ID ","Location","","Comany",""House Name" Road",
In SQL 2012, this fails with the error message, cannot find the text qualifer for field.
To get around this, we are having to import the data into a Dirty Data column of aTEMP table, ID, Dirty Data, Clean data - perform multiple updates and change the text qualifier and ensure they are only changed in the right places so we can keep the ". In this example, we changed the text qualifier to PIPES.
After these updates, we then export the data from CLEAN data back out to CSV, then reimport it into the origional destination table with a new text qualifer.
I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.
View 3 Replies View RelatedHi,
I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View 2 Replies View RelatedHi all,
i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.
I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format) using the SQL Server Management Studio, for
example.
Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!
cheers
Dominic
Hi,
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
Thanks.
Hi,I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it.
I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns)
For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal.
<ProductMergeAudit> <before> <table name="table1" description="Test Desc"> <product id="OldVal"> </table> </before> <after> <table name="table1" description="Test Desc"> <product id="NewVal"> </table> </after></ProductMergeAudit>
Hi,
I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want.
The beginning of the SQL query looks like this:
FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND
It is the (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM.
I hope someone out there can help me with making this comparison.
I appreciate your help.
Bill
Hi All
Iam trying to Get a text field value i wrote this code
DECLARE @ptrval varbinary(16)
DECLARE @length bigint
SELECT @ptrval = TEXTPTR(Template), @length = LEN(Template)
FROM #TEMPLATE
READTEXT Template.#TEMPLATE @ptrval 0 @length
but i need to put the result into a text var
is that possible or not and if it possible any one could help me with that
Hi everyone,
I'm extremely new to SQL so be nice
I am attempting to write a script to add onto the end of a text field the words " -- Disposed " (About 60 rows worth).
The field is a TEXT field, so unlike a varchar field I can't just use Update as shown below.
Code:
Update AR_Primary_asset
Set AR_Primary_asset.description = AR_Primary_asset.description + ' -- Disposed'
Where AR_Primary_Asset.ASSET_REF in ('1','2','4')
I found on the Mircosoft pages about UPDATETEXT, but this only seem to work to update one row (In the case below Asset_ref = 3, was the only row effected) .
Code:
DECLARE @Dispose binary(16)
SELECT @Dispose = TEXTPTR(DESCRIPTION)
FROM AR_PRIMARY_ASSET
WHERE AR_Primary_Asset.ASSET_REF in ('1','2','3')
UPDATETEXT AR_PRIMARY_ASSET.DESCRIPTION @ptrval null null ' -- Disposed'
So i wrapped it into a cursor, this worked on my test SQL server which runs SQL2005.
Code:
DECLARE @Dispose varbinary(16)
DECLARE cursor1 CURSOR FOR
SELECT TEXTPTR(DESCRIPTION)
FROM AR_Primary_Asset
Where AR_Primary_Asset.ASSET_REF in('1','2','3')
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @Dispose
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATETEXT AR_Primary_Asset.DESCRIPTION @Dispose NULL NULL ' -- Disposed'
FETCH NEXT FROM cursor1
INTO @Dispose
END
CLOSE cursor1
DEALLOCATE cursor1
But when it was run on our SQL2000 server it gave the following error message
Quote: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
I've never used vars, cursors, updatetext or even text fields before. So maybe I am going about it totally the wrong way.
Is anyone able to tell me a better way to write this? or how to make it compatible to SQL2000?
CREATE TABLE [dbo].[instructions](
[site_no] [int] NOT NULL,
[instructions] [text] NULL
)
Select top 3 * from instructions
Output
Site_noInstructions
20Request PIN then proceed
21Request PIN if wrong request name
22Request PIN allowed to use only numbers
All text instructions start with “Request PIN” but after that the text are different for every site_no
I need insert in all site_no rows and after the “Request PIN” the text “and codeword” keeping the current rest of text
Desired output
Site_noInstructions
20Request PIN and codeword then proceed
21Request PIN and codeword if wrong request name
22Request PIN and codeword allowed to use only numbers
Application is ocr'ing tiff image files and then storing the resultant text data in a text field in SQL 2005 database. This field is then used with the full text catalog.
All works fine,
However, am I using the correct field type to store the text files for efficiency and space saving?
If I use a binary field, does this reduce the size of the database by compressing the text data in the binary field? Also, is there a limit as to the size of text file that I could store in a binary field?
It would be good to get feedback on this before I go too far down the wrong road.
So, text field to store the text data or binary field to store the actual text file?
I am trying to use the RTRIM command but it does not seem to be working. If I perform
select rtrim(name) from table
it returns the row with the spaces in it anyway. ???
If Rtrim doesn't catch space at the end is there a way to catch strings that match but don't seem to get selected correctly when matching?
View 4 Replies View RelatedCould somebody please give me a syntax that I can use to trim spaces. I have used and I still have the spaces. :
UPDATE MAP SET ROAD = RTRIM (STREET)
UPDATE MAP SET ROAD = LTRIM (STREET)
We are trying to clean up spaces at road intersections. Thanks for your assistance.
I have some data that contains spaces both before and after the text string, and now I'm wondering what would be the best method to remove these blanks (sometimes there are no blanks, so I can't check with a specifik width)?
Is it possible to do something like:
set foo = ltrim(rtrim(foo))
or do I have to split it into 2 steps?
This trimming will be done in update & insert statements
// Pati
How do i remove carriage returns in SQL Server ? each of the lines have a carriage return as well as in front and back of the text.
Keith Waltin
Transport Ticketing Authority
03 9651 9066
I've tried the
update test.dbo.test
set bodytext1 = ltrim(rtrim(bodytext1))
but the whitespace/carriage returns still exists in the back and front of the text ? Anyone got any ideas ?
Hi!
I want to put a trigger on insert (bulk insert). It´s supposed to do RTRIM and LTRIM on the incoming data to a specific column in a table. The data comes from a textfile.
What to do? Totally stuck..
Thankful for all help!
//lonil
can anyone explain me what happens when we write the above for a col.
I k now it remove spaces but can anyone explain with exmpls
thanks
Could anyone explain why this happens:
-- All outputs works but only the first should
select 'works' where '1' = '1'
select 'works' where '1' = '1 '
select 'works' where '1 ' = '1'
Seems to me like trailing blanks are automatically trimmed, why?
You all have been so much help, but I've discovered yet another problem. I'm trying to clean up my table using the following command:
UPDATE dbo.TableName
SET First_Name = LTrim(RTrim(First_Name))But it does not seem to have any effect. Thoughts? Thanks!
I have a DistributorInvoiceNumber that can end with in 'R', 'A', 'CRR' or 'CR'.I am trying to write a case statement like so:
CASE WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'A'
THEN 'ADJ'
WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR'
THEN 'CREDIT'
ELSE NULL END AS 'Status'
For the most part the code is working, with the exception of the fields that just end in 'R'. An example of this is 471268R, 2525125901CRR, 11100325230CR
Basically if the number ends with an A, then its an Adjustment, if it ends with JUST an R, then its a Reversal; if it ends with just a CR then it is a Credit and if it ends with CRR then it is a Reversal (Credit Reversal). How can I differentiate between the different R's since three of them end with R? Would I use a RTRIM command somehow?
I understand that for SQL Server rtrim is not needed in where clause equates because SQL Server
automatically trims the spaces. Is rtrim necessary when comparing to a host variable since rtrim is a deterministic function? It it needed in the following example
Select ....
where
TEXT = rtrim(:I--HV-001)
TEXT is a varchar column in the SQL Server database and it is getting compared to the host variable.
I am loading a dimension using a distinct query.There are duplicates coming through and the only differnce is a trailing space on one of the columns.
RTRIM is not removing the space.
how i can fix it?
When is RTRIM needed in a Select ... where clause. I noticed that if I have a column named TEXT varchar(17) which is varchar and in the where clause I state where
TEXT = 'This is the text'
or I state
TEXT = 'This is the text ' followed by 4 spaces
The equate still works - so when do I need RTRIM?
Do I need RTRIM for a host variable:
...where TEXT = RTRIM(:VAR_001)
if the host variable is the same length as the TEXT column field in the SQL Server 2005 database?
I'm not sure about why I'm not able to remove spaces even after trimming them. Below is the result of query I'm usning.
select distinct LTRIM(RTRIM(Promotion_Code)) Promotion_Code
--, count(Promotion_code)
from dbo.Marketing_Promotion_Tb
where Promotion_code like '%1BTPIZZA%'
Result :
Promotion_Code Length
1BTPIZZA 10
1BTPIZZA 8
I have the following Table:
ORDER_ID SKUS
12345678 SCINC, SCNRQ, SRPPR
All the values in the 'SKUs' column are combined together, seperated by a comma.
Is there an easy to way "de-pivot" this so that the Order ID gets repeated as follows:
ORDER_ID SKU
12345678 SCINC
12345678 SCNRQ
12345678 SRPPR
Here's some code:
SELECT ORDER_ID, SKU FROM ORDERS