Converting DataType=TEXT to DataType=STR

Hi,

I have an input text file which contains line(s) which can have more than 8000 characters.

I am able to read the lines in this file by specifying that the input column is data type = Text Stream [DT_TEXT] and I can then write the lines to another text file.

But, what I want to do is to use only the right-most 8000 characters. When I try to convert the input column to data type = String [DT_STR] using the Data Conversion transformation then I get the error:-

The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.

I cannot use the string manipulation functions e.g. RIGHT or SUBSTRING in a Derived Column transformation because these functions do not work with DT_TEXT data.
Any Ideas?!

Thanks.You'd only need *1* line of custom code in a Script component transformation to extract your rightmost 8000 characters before moving on to the data type conversion.

-Doug|||Thanks Doug ... Did you mean a Script Component which looks something like:-

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim ascii As Text.Encoding

Dim asciiChars(10) As Char

ascii.GetChars(Row.InputColumn.GetBlobData(1, 1), 0, 1, asciiChars, 0)

Dim asciiString As New String(asciiChars)

MsgBox(asciiString)

End Sub

End Class

When I run the package it fails at the ascii.getchars line with the following error message:-

Error: 0xC0047062 at Data Flow Task, Script Component [106]: System.NullReferenceException: Object reference not set to an instance of an object.

All I am trying to do is extract the first byte and convert it to an ascii char (before I go on to work out the lengths to extract the right-most 8000 chars etc.)

Is this what you had in mind? Or, is there another way to do it?

Many Thanks.

|||This should do what you want. You will need to import System.Text.Encoding.

Dim Start As Integer = Max(0, CInt(Row.InputColumn.Length) - 8001)
Dim Length As Integer = Min(8000, CInt(Row.InputColumn.Length))
Dim asciiString As String = ASCII.GetString(Row.InputColumn.GetBlobData(Start, Length)

|||Just to confirm that Jay's suggestion works. Many Thanks.sqlsql

Converting data types

I've inherited a monster database that needs updating. It has a field called Birthdays that has been a text field and I want to convert it to datetime. The data entry people have been sloppy from time to time and some of the values generate errors when you try and change datatypes. Is there a way to change the datatype and have the oddball values left blank without bombing out the conversion process?

Thanksheh. how big is the database?

if it's not that big, then you can simply store them in a temp table, and move the values over one by one in a cursor style fashion. If it's absolutely enormous, I'd actually create another table just to store the data, and the same cursor fasion. Just make sure you put them in transactions so you can rollback accordingly based on any errors generated.

Since it's a one shot deal, you can even do it out of the database, and just manipulate all the values via code, then do the alter so it doesn't cause any problems.

Do you know specifically what the bad dates are? If so, via code, you can convert the dates based on how the errors were inputted, and update those records. :-\

Converting Data Types

I need to be able to convert a numeric data type to a text data type.

I've tried the TO_CHAR and the DBCONVERT functions but I am returned an error stating that they are not recognized functions.

I'm using the Query Analyzer on a Windows 2000 SQL Server.

Any help would be appreciated.

TechRickYou can use the cast or convert functions.|||Thanks,

I was just reading up on the CAST function. However, I've not been able to make it work yet.

In correction to my original post, I need to take a MONEY data type and convert it to a TEXT data type. I hope this possible.

Thanks again,

TechRick|||Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles|||You can also do it this way:

select cast(cast(price as varchar(30)) as text) from titles

But since the conversion is implicit between varchar and text you don't need to do this.|||Thanks again for the help. Your suggestion works great but when I try to update the data I get this:

Server: Msg 260, Level 16, State 1, Line 31
Disallowed implicit conversion from data type varchar to data type money, table..., column 'PRICE'. Use the CONVERT function to run this query.
Server: Msg 257, Level 16, State 1, Line 31
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

I'll let you know once I get it worked out.

Best Regards,
TechRick|||The way the error is reading is that you are trying to put a varchar into a money column - is that correct ?|||Yes, I have a large list of items collected from various tables and there are prices associated with these items. A good majority of these items have no price (.0000) and I would like to exchange the .0000 price for a 'CALL' or something similar.

I could not update the records with a "text" substitute so I thought I would convert the data type to TEXT so I can plug in whatever I want.

Thanks again,
TechRick|||Why did you choose text over varchar ? Could you post your update statement as well as the definition of the table you are updating ? You have to explicitly convert varchar to money and vice-versa using either cast or convert. But I am a little confused about the first error you received - why are you trying to put a varchar into a money column ?|||You'll have to excuse me, I've only been working with SQL for less than 3 weeks. I've been learning as I go (with my Dummies and Sam's books and this forum). I've already written a few complex queries and as for this particular situation, this is the last obsticle I need to overcome to put this query to rest.

Based on the error I was getting I assumed that I was doing something wrong but I didn't have the time to completely research it. I'll pick it back up on Monday. I think I need to understand the convert and cast functions better before I can make use of them. I'll be working towards that end.

As for now, I'm away from work and don't have easy access to the code. I'll post it Monday after I tinker a little more.

Thanks for the help.

TechRick|||Ah, by "convert" you mean that you would like to change the data type of a column from money to varchar so that you can store a mix of data in a single column. The easy answer is to just change the data type of the column in Enterprise Manager. If SQL Server can find a reasonable way to preserve the data, it will. Thereafter you can store any character data in the column, e.g. "Call" or "Operators are standing by!".

The "correct" answer is rather different, and a valid subject for debate. If you do not have a price for an item, the correct representation in the database should be different from a free item. For example, you might use the value NULL to indicate "call for price" and $0.00 for a free item. Alternatively, it may make more sense in your application to have a separate means of flagging items for which you don't want to publish a price, qualify for free shipping, have quantity discounts, ... . More columns or tables may be needed.|||Agreed about the 'correct' answer. Fortunately and unfortunately, I didn't write the application so I'm working with what's there.

I managed to get it taken care of by adding the convert on my select statment.

SELECT UPPER(ITEM), DESCRIPT, Q_STK, QTY_RESERVE, CAST (SELLPRICE AS VARCHAR(15)),...

By doing it this way I am able to manipulate the data any way I want.
Add 'CALL', 'FREE', etc.

Thanks all for the help. One last item and this query is behind me.

Best Regards,
TechRick|||Originally posted by rnealejr
Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles

rnealejr, I noticed as I was going back through all these posts that you were right on the money a long time ago! Thanks for the help. I think I was trying to perform an update on the column using the cast rather than taking the data in converted from the start. Anyhow, just wanted to thank you for your help. Too bad I had to learn the hard way.

Best Regards,
TechRick|||Thanks for the email and compliment as well as a good pun (right on the money) - I enjoyed that.

Good luck.|||Another handy tool for fudging return values within a query is throwing in a CASE, e.g.:

select Description, ServingSize, case when Price<>0.0 then Convert(VarChar,Price) else 'Call' end as 'AdvertisedPrice'
from PiecesParts where Fused=1

Note that there are two slightly different versions of CASE. One lets you test a single expression against multiple values, while the other lets you test multiple expressions.

You can swindle a lot of logic into a CASE or nested CASEs. For example, it could check for quantity price breaks or apply discounts based on data from other tables or variables. The result is just another (computed) column in the recordset returned from the query. (As such, it isn't writable.)

Converting CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT!

Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz AnsariIf you are, in batch, converting all types, you couls just script the database using Enterprise Manager, and then do a search and replace for each of the types. Be aware that converting from varchar to nvarchar could cause problems if rows already contain over 4000 characters.|||I have just created a small VB utility program which can take list of files (.sql files) and uses regular expression to replace data types in SPs files. I am still looking for some automated tool.

Regards

Converting Blob fields to Text on a Report

On SQL Reporting Services, Blobs (type = image) do not even get exposed
to the report. Note: I am using blobs since I need virtually
unlimited text. So, to get the data out of blobs, I normally use ADO
methods of getchunk and actualsize. I tried creating a class with the
logic that I needed. I could pass it SQL and it would return the value
of the blob as a text string. Then I created the following Custom Code
in the report:
Public Function BlobText(strColumn$, strTaskID$) As String
dim t as object
t = createobject("MyReportClass.Functions")
BlobText = t.blob2text("MySQLServer","select " & strColumn & _
" from SQLDatabase..task where ID = '" & strTaskID & "'")
End Function
When I preview the report it works perfectly. When I deploy the
report, the textbox that calls the method puts "#Error" on the report.
There does not seem to be any meaningful log to help.
Anyone have any guesses?
Thanks,
SteveHi Steve,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to write
embedded code in the reporting services. If I have misunderstood your
concern, please feel free to point it out.
Based on my knowledge, you are recommanded to read the article below
Writing Custom Code in SQL Server Reporting Services
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
Embedded Code In Reporting Services
http://odetocode.com/Articles/130.aspx
If this still does not resolve your issue, would you please generate a
sample rdl file with your function based on AdventureWorks database and
send it to me? my direct email address is v-mingqc@.online.microsoft.com
(remember remove "online" before you click SEND as "online" is only
prepared for SPAM), you may send the file to me directly and I will keep
secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Lost Customer, Close: Not Resolved

Converting binary to text

Hey,

Another 'must be simple' question.. I'm wishing to convert a binary
field into a textual representation of that binary data. i.e. come out
with what the query analyser would display, and not try to convert the
binary into ascii/whatever encoded text.

Cheers for any clues,
ChrisNot Me wrote:
> Hey,
> Another 'must be simple' question.. I'm wishing to convert a binary
> field into a textual representation of that binary data. i.e. come out
> with what the query analyser would display, and not try to convert the
> binary into ascii/whatever encoded text.

Sorted now, through the master.dbo.fn_varbintohexstr function... took an
unnatural amount of google-ing tho :)

Converting Binary Image to Readable Text

I have a table that contains the following two columns:

BITS (image(16))
BIT_LENGTH (int(4))

When I look at the table, I see "OLE Object" in the BITS column. What
syntax should I use in a SELECT statement to convert the binary image
info contained in "BITS" into simple text that I can read? What role
does the BIT_LENGTH field play?An image column can store any binary data, so you need to know what the
data represents before you can display it - it could be a Word
document, a PDF, an MP3 etc. Do you already know exactly what the
column is storing ('text' is the normal data type for large amounts of
text data)? As for BIT_LENGTH, it could mean anything, depending on the
data model - there's no need for an image column to have an associated
length column with it.

It sounds a little like you've just taken over a database from someone
else? If so, you might want to review this chapter from the SQL2000
Resource Kit, which provides more detailed information about storing
and retrieving BLOBs:

http://www.microsoft.com/technet/pr...art3/c1161.mspx

Simon

converting a text field to number

I have a table with over a million rows and one of the fields contains
amounts of money in text format.
What is the most efficient way of converting this field to a number
format that I can sum on?

Regards,
CiarnHi Ciaran,

Before converting the type of the col, make sure that there are no
invalid values in that column. You can pull them out by

SELECT colName FROM tabName WHERE IsNumeric(colName) = 0

Alter your column type by

ALTER TABLE tableName ALTER COLUMN colName NUMERIC

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Nazeer Oasis (nazeerpp@.indiatimes.com) writes:
> Before converting the type of the col, make sure that there are no
> invalid values in that column. You can pull them out by
> SELECT colName FROM tabName WHERE IsNumeric(colName) = 0
> Alter your column type by
> ALTER TABLE tableName ALTER COLUMN colName NUMERIC

Unfortunately, this may still fail, since IsNumeric will approve of
values than converts to float or money, but not to numeric. Also, I
say that it's extremely bad practice to say numeric without specifying
scale and precision. You get some defaults, but these may not be what
you expect.

As for the original query, the easy way is:

SELECT SUM(convert(int, textcol)) FROM tbl
or SELECT SUM(convert(money, textcol)) FROM tbl

But this will of course fail if there are strings that does not convert.

If all data is integer, that is the text is undelimited and there are
no decimals, then it's pretty easy to test:

textcol NOT LIKE '%[0-9]%'

If the text can delimiters and decimals, it can become quite hairy
to filter.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> If all data is integer, that is the text is undelimited and there are
> no decimals, then it's pretty easy to test:
> textcol NOT LIKE '%[0-9]%'

This is wrong. I forgot a ^:

textcol NOT LIKE '%[^0-9]%'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Converting a parent-child table into a genrational table / text file

I need to convert a parent - child table into another table or a text file containing in a generational format.

eg.

child / parent / grand parent / great grand parent / ....

Does anyone have a stored procedure of code to do this?

I'm working with a dimension having 250,000 + members, writing code is fast enough for much smaller hierarchies but with a dimension this size we need something fast.

Thank you

Hi Rod,

Not sure if this is the same problem as you posted in TSQL under "Adjacency List' - as suggested there, recursive CTE would be one approach in SQL Server 2005:

>>

with GenTable(LeafKey, LeafName, GenNum, AncestorKey, AncestorName) as

(select do.OrganizationKey as LeafKey, do.OrganizationName as LeafName,

1 as GenNum, do1.OrganizationKey as AncestorKey,

do1.OrganizationName as AncestorName

from dbo.DimOrganization do

join dbo.DimOrganization do1

on do.ParentOrganizationKey = do1.OrganizationKey

where not exists(select *

from dbo.DimOrganization do2

where do2.ParentOrganizationKey = do.OrganizationKey)

union all

select gt.LeafKey, gt.LeafName,

gt.GenNum + 1 as GenNum, do.ParentOrganizationKey as AncestorKey,

do1.OrganizationName as AncestorName

from GenTable gt

join dbo.DimOrganization do

on gt.AncestorKey = do.OrganizationKey

join dbo.DimOrganization do1

on do.ParentOrganizationKey = do1.OrganizationKey)

select LeafKey, [1] as Gen1Key, [2] as Gen2Key, [3] as Gen3Key

from (select LeafKey, GenNum, AncestorKey

from GenTable) gt

Pivot (Max(AncestorKey)

for GenNum in ([1], [2], [3])) as pt

order by LeafKey

--

3 14 2 1
4 14 2 1
5 14 2 1
6 14 2 1
7 14 2 1
8 2 1 NULL
11 9 1 NULL
12 9 1 NULL
13 10 1 NULL

>>

|||Hi Deepak,

Thank you very much, it's been as long day and I'll look closer in the morning. It looks vey helpful. I should of mentioned that I have to do the with 2000 and 2005 but this likes I'm half way there and on the right track

Thanks again,

Rod

Converting a Column in XML

Hello NG!
We would like to convert an Column which contain XMLs to an XML Column.
Actually it ist formatted as plain Text in a big Table with approx. 300.000
Lines.
Trying to convert the Type of the Column to XML does not succed because of a
TimeoutError everytime we try it.
Do someone has an idea how we can solve this Problem?
Thank xou very much
MarkusHave you tried exporting/splitting your table into say, 3 or 6 equal parts
as physical temp tables, convert your field into XML there, and then
re-import into the orginal table? You might need to drop some FKs to do
this.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Markus Nistim" <manistim@.hotmail.com> wrote in message
news:%23ZqoniMNIHA.3516@.TK2MSFTNGP02.phx.gbl...
> Hello NG!
> We would like to convert an Column which contain XMLs to an XML Column.
> Actually it ist formatted as plain Text in a big Table with approx.
> 300.000 Lines.
> Trying to convert the Type of the Column to XML does not succed because of
> a TimeoutError everytime we try it.
> Do someone has an idea how we can solve this Problem?
> Thank xou very much
> Markus
>

Convert to Number IF it is a number

Pardon my newbieness:
I have a column that could contain numbers or text (not my idea). All
of the values are returned as text. I want to return the value as a
number if it is a number, otherwise (of course) leave it as text. What
can I do?Return the value to where?
A single column in a resultset has exactly one datatype.
Can't your receiving application or front end code figure out what kind of
data it is?
Why are you storing numbers and strings in the same column?
A
"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>|||> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
Use the ISNUMERIC function. Check the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>
A column only has a single datatype so I'm going to assume you require a
result set with two columns: one VARCHAR, one INTEGER. I will also assume
that when you say "text" you really mean "VARCHAR". Doing this using the
TEXT datatype would be a bit more tricky. That's too many assumptions but if
you don't post a more precise spec then assumptions and guesses are what you
will get. You can help us to help you better by taking the advice in the
following article:
http://www.aspfaq.com/etiquette.asp?id=5006
Here's my example:
CREATE TABLE tbl (x VARCHAR(10) PRIMARY KEY) ;
INSERT INTO tbl (x)
SELECT '123' UNION ALL
SELECT '99A' UNION ALL
SELECT 'ABC' ;
SELECT x, CAST(num AS INTEGER) AS num
FROM
(SELECT x,
CASE WHEN x NOT LIKE '%[^0-9]%' THEN x END AS num
FROM tbl) AS T ;
Result:
x num
-- --
123 123
99A NULL
ABC NULL
(3 row(s) affected)
Hope this helps.
David Portas
SQL Server MVP
--|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23R%23NQQKEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Use the ISNUMERIC function. Check the syntax in Books OnLine.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
Before using ISNUMERIC Tod should be aware of its limitations. Read:
http://www.aspfaq.com/show.asp?id=2390
David Portas
SQL Server MVP
--|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.

CONVERT text to varchar

I am upgrading another Access database to SQL Server and have hit a small
obstacle. The database has a column named Remarks and is of the datatype
text. I used the following query to find the maximum size of the Remarks
column,
SELECT MAX(Datalength(Remarks))
FROM Admissions
This returned 939.
I decided to use varchar instead, so I inserted a new column, RemarksVar and
used the following query to convert Remarks to RemarksVar,
UPDATE Admissions SET
RemarksVar = CONVERT(varchar(1000),Remarks)
Now when I look at the data, it looks fine, but when I use the following
query,
SELECT Datalength(Remarks) AS OLDLEN, LEN(RemarksVar) AS NEWLEN
FROM Admissions
It returns the old lengths and new lengths. The issue is that the new
lengths are EXACTLY half of the old lengths. Can anyone explain to me why
this happened like this?
Thanks,
DrewI think either the old column is NTEXT or the new column is NVARCHAR. N
means Unicode data and it occupies twice the space. This is reflected in
DATALENGTH() (which measures the number of bytes) but not LEN() (which
measures the number of characters). See this simple example:
DECLARE @.foo NVARCHAR(32), @.bar VARCHAR(32)
SET @.foo = N'splunge'
SET @.bar = 'splunge'
SELECT LEN(@.foo), DATALENGTH(@.foo),
LEN(@.bar), DATALENGTH(@.bar)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:us3D1dmoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>I am upgrading another Access database to SQL Server and have hit a small
>obstacle. The database has a column named Remarks and is of the datatype
>text. I used the following query to find the maximum size of the Remarks
>column,
> SELECT MAX(Datalength(Remarks))
> FROM Admissions
> This returned 939.
> I decided to use varchar instead, so I inserted a new column, RemarksVar
> and used the following query to convert Remarks to RemarksVar,
> UPDATE Admissions SET
> RemarksVar = CONVERT(varchar(1000),Remarks)
> Now when I look at the data, it looks fine, but when I use the following
> query,
> SELECT Datalength(Remarks) AS OLDLEN, LEN(RemarksVar) AS NEWLEN
> FROM Admissions
> It returns the old lengths and new lengths. The issue is that the new
> lengths are EXACTLY half of the old lengths. Can anyone explain to me why
> this happened like this?
> Thanks,
> Drew
>
>|||You are correct... it was ntext. Sorry I didn't specify, I guess I didn't
think there was much difference between text and ntext.
Whenever I upsize an mdb database to SQL server, it always converts the text
(in Access) fields to nvarchar. Should I change over all of these nvarchar
to varchar?
Thanks for your help, I just wanted to make sure that I wasn't missing data!
Drew
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2382YYhmoFHA.3312@.tk2msftngp13.phx.gbl...
>I think either the old column is NTEXT or the new column is NVARCHAR. N
>means Unicode data and it occupies twice the space. This is reflected in
>DATALENGTH() (which measures the number of bytes) but not LEN() (which
>measures the number of characters). See this simple example:
> DECLARE @.foo NVARCHAR(32), @.bar VARCHAR(32)
> SET @.foo = N'splunge'
> SET @.bar = 'splunge'
> SELECT LEN(@.foo), DATALENGTH(@.foo),
> LEN(@.bar), DATALENGTH(@.bar)
>
>
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:us3D1dmoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||> Whenever I upsize an mdb database to SQL server, it always converts the
> text (in Access) fields to nvarchar. Should I change over all of these
> nvarchar to varchar?
If you don't need Unicode support, yes, however depending on the size of
your tables the conversion might not be worth the effort. If you're going
to keep any ntext/nchar/nvarchar then please read:
http://www.aspfaq.com/2354
http://www.aspfaq.com/2522|||Whether you need unicode support or not depends on your data. Do you
currently store any unicode characters? Can you expect unicode characters in
the future?
ML|||I don't foresee any reasons for including unicode in my database. I may
regret this statement down the road, but these databases are localized and
not used abroad.
Thanks,
Drew
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A4A87FAB-09B4-4F4D-92BB-F0E1980F7802@.microsoft.com...
> Whether you need unicode support or not depends on your data. Do you
> currently store any unicode characters? Can you expect unicode characters
> in
> the future?
>
> ML|||Thanks for your reply! Since I am upgrading these databases from Access, I
have to do quite a bit to the database to get it upgraded. The programmer
before me didn't use foreign keys, so I add them in and there are some other
problems that I need to fix.
I think I will convert my n's to regulars to save space.
Thanks,
Drew
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ealzkwmoFHA.2156@.TK2MSFTNGP09.phx.gbl...
> If you don't need Unicode support, yes, however depending on the size of
> your tables the conversion might not be worth the effort. If you're going
> to keep any ntext/nchar/nvarchar then please read:
> http://www.aspfaq.com/2354
> http://www.aspfaq.com/2522
>

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx
.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?
I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?
|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Convert Text to Time

Dear all,

I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

Thank you

GeorgeSQL Server is pretty good about implicitly converting text to time. If the time for is really odd, then you may need to use the CONVERT function or create a custom function.

Can you do this for all 30 tables in a single query? MAYBE using a union query, but don't count on it.|||I am not using SQL Server but Microsoft Access.

I time had an odd format but i have managed to manipulate it to the format 00:00:00.

I only need to convert the field from text to date/time. I have tried convert() and CDate() but i cannot make it to work.

My table is named "01" and the field "Duration".

Can anyone provide me with the full code to make the change.

I will use "Macros" and "RunSQLQuery" to make the converion for all tables.

This is the last problem i have to solve in order to make it work. I have been working on this database for the last week.

Please note that i am new in SQL with MS Access. I have started only one moth ago|||I would suggest that you post this question in the MS-Access (http://www.dbforums.com/f84) forum. They have more experience with the Access GUI and might be able to give you better suggestions.

I tried using Cdate("13:23:45") in an Access query, and it worked nicely for me. I suspect that that is the conversion that you need, but I'm not certain about what code you need to derive a properly formatted time string.

-PatP|||Ok, I thought of something else.

There is no need to change the data type of the table. I just created a query with all the fields of the table but for Duration i put: CDate([Duration]). The query returns the results as Date/Time and from there i can perform the calculations i require.

It works.

Thanks again, problem solved.|||Gee, you just have to love it when you inadvertanly solve a problem!

-PatP

Convert Text to Time

Dear all,

I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

Thank you

GeorgeFirst, you will need to use the CAST or CONVERT SQL function to make your text a datetime field (assuming you need the seconds in the time otherwise smalldatetime will work also). Then use the DATEPART function to return just the time portion of the field.|||I am new in SQL and the book i use doesn't offer much help in th convert function.

Can you give me the sql statement to convert the field duration ofthe table 01 from text to datetime?

Thanks|||MSDN is a big help. See the documentation on Convert() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) either online or in your copy of Books Online (which is installed as part of the SQL client tools).

-PatP|||I still can't figure it out.

I use the following:

UPDATE 01
set CONVERT (datetime, Duration);

and

UPDATE 01
set CAST (Duration as datetime);

I get a syntax error after (|||You'll have to post at least a few lines of code for me to be able to help you. I can't figure out what you want from what you've posted so far.

-PatP|||My table is named "01".

One of the fields is "Duration".

The table is imported from a csv file. The Duration is not in a valid date/time format so i have to import it as "Text", modify it and convert is to "Date/Time".

I can easily do this from the design view of the table but since i have 31 similar tables, i require a faster way (i don't want to go through all 31 tables to make the changes, it takes too long). The fastest way i can think is through SQl.

So, it would be much apreciated if someone can provide me with the command to update the date type of "Duration" field from "Text" to "Date/Time" using SQL or a Macro.

I will use the command to do the same change for all 31 tables.

This is the last problem i have to solve before i can make the database work.

Convert text to number

Is there any way to convert a text field to a number field and drop any data
that has an alpha character in it? I am able to override the message in
Access but I am not sure if you can in SQL.
For ex.
"1234AB" Don't want this data
"123456" Want this data
Thanks,
LisaIf the numbers are positive integers, then try:
use northwind
go
create table t (
colA varchar(25)
)
go
insert into t values('1234AB')
insert into t values('123456')
go
delete t where patindex('%[^0-9]%', colA) > 0
go
alter table t
alter column colA int
go
select * from t
go
drop table t
go
if not, use the functions from this link.
What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"Barce5" wrote:

> Is there any way to convert a text field to a number field and drop any da
ta
> that has an alpha character in it? I am able to override the message in
> Access but I am not sure if you can in SQL.
> For ex.
> "1234AB" Don't want this data
> "123456" Want this data
> Thanks,
> Lisa

Convert Text to Int

I am trying to write a query in access that will pull results that are
in the database in a text field and convert to where I can get a
average including decimals. Any Ideas?

--------Start SQL------------
SELECT AVG(CAST(dbo_sur_response_answer.answer_text AS int)) AS
avg_correct
FROM (dbo_sur_response_answer
INNER JOIN dbo_sur_subitem
ON dbo_sur_response_answer.subitem_id = dbo_sur_subitem.subitem_id)
INNER JOIN dbo_sur_response
ON dbo_sur_response_answer.response_id = dbo_sur_response.response_id
WHERE (((dbo_sur_response.completed_yn)="Y") AND
((dbo_sur_subitem.subitem_id)=478));
--------End SQL------------Integers do not have decimal places.

CInt() will convert a number held in a string to an Integer type.
CDouble() will convert a number held in a string to a Double type.
Doubles have decimal places.|||On 11 Jan 2006 13:14:43 -0800, 2redline@.gmail.com wrote:

>I am trying to write a query in access that will pull results that are
>in the database in a text field and convert to where I can get a
>average including decimals. Any Ideas?
>
>--------Start SQL------------
>SELECT AVG(CAST(dbo_sur_response_answer.answer_text AS int)) AS
>avg_correct
>FROM (dbo_sur_response_answer
>INNER JOIN dbo_sur_subitem
>ON dbo_sur_response_answer.subitem_id = dbo_sur_subitem.subitem_id)
>INNER JOIN dbo_sur_response
>ON dbo_sur_response_answer.response_id = dbo_sur_response.response_id
>WHERE (((dbo_sur_response.completed_yn)="Y") AND
>((dbo_sur_subitem.subitem_id)=478));
>--------End SQL------------

Hi 2redline,

I doon't know much about Access, but the following should work in SQL
Server. (I assume that using SQL Server is an option, since you
crossposted this to a SQL Server group).

SELECT AVG(CAST(ra.answer_text AS decimal(5,2))) AS avg_correct
FROM dbo.sur_response_answer AS ra
INNER JOIN dbo.sur_subitem AS si
ON si.subitem_id = ra.subitem_id
INNER JOIN dbo.sur_response AS r
ON r.response_id = ra.response_id
WHERE r.completed_yn = 'Y'
AND sit.subitem_id = 478;

I introduced aliasses and changed spacing to make the query more
readable, changed the Access double quotes to standard SQL single quotes
and removed all the unneeded parenthese. The only actual change I made
was CASTing to decimal(5,2) instead of CASTint to int. The average of a
bunch of integers will be integer as well; if you want a result with
decimals, you'll have to use a datatype with decimals (such as
decimal(5,2), which specifies 3 digits to the left and 2 to the right of
the decimal point).

--
Hugo Kornelis, SQL Server MVP|||Had one correction to the last line and removed the t from sit to be
si.

Query Analyzer gives an error of
"Server: Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type text to decimal is not allowed."

This is the same error I got during previous attempts also.

It makes me believe it is something to do with the field type. It is
Text, 16, allow nulls

The following working sql will give me each response but no average
........
SELECT dbo_sur_response_answer.answer_text
FROM (dbo_sur_response_answer INNER JOIN dbo_sur_subitem ON
dbo_sur_response_answer.subitem_id = dbo_sur_subitem.subitem_id) INNER
JOIN dbo_sur_response ON dbo_sur_response_answer.response_id =
dbo_sur_response.response_id
WHERE (((dbo_sur_response.completed_yn)="Y") AND
((dbo_sur_subitem.subitem_id)=[enter SubID]));

Results
------
25.1
20.9
1.12

Any more ideas?
Thanks|||2redline wrote:
Explicit conversion from data type text to decimal is not allowed."

BOL gives the follwing defintion for text:

Variable-length non-Unicode data in the code page of the server and
with a maximum length of 231-1 (2,147,483,647) characters. When the
server code page uses double-byte characters, the storage is still
2,147,483,647 bytes. Depending on the character string, the storage
size may be less than 2,147,483,647 bytes.

I think of this as analogous in some ways to the JET Memo type.

I am guessing that:

the error message is incorrect and that actually this field is of type
varchar or similar,

or

the creator of this table made some careless error in using a text
field to hold string data that might be converted to numeric data type.

Should my guesses be right, a solution is to change the datatype of the
field (carefully!) or ... I suppose that some legerdemain might be
attempted in the T-SQL to convert the Text to VarChar to Money or
whataver .|||On 12 Jan 2006 05:31:14 -0800, 2redline wrote:

>Had one correction to the last line and removed the t from sit to be
>si.
>Query Analyzer gives an error of
>"Server: Msg 529, Level 16, State 2, Line 1
>Explicit conversion from data type text to decimal is not allowed."
>This is the same error I got during previous attempts also.
>It makes me believe it is something to do with the field type. It is
>Text, 16, allow nulls

Hi 2redline,

Lyle's answer gives you the reason for the error.

You can get around this by first casting from text to varchar, then
casting that to decimal:

SELECT AVG(CAST(CAST(ra.answer_text AS varchar(40)) AS
decimal(5,2))) AS avg_correct
FROM dbo.sur_response_answer AS ra
INNER JOIN dbo.sur_subitem AS si
ON si.subitem_id = ra.subitem_id
INNER JOIN dbo.sur_response AS r
ON r.response_id = ra.response_id
WHERE r.completed_yn = 'Y'
AND sit.subitem_id = 478;

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

Convert text to float in SQL Statement

Hi,

Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".

I do get the answer but its not the correct figure. My SQL statement is as follow:

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName

Please help.Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.|||Originally posted by Crespo-n00b
Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.

I've tried it and it still gives me a wrong answer.
I should have 45299 + 7832.5 = 53131.5, but I kept getting
13310.

Any more ideas?|||A Miracle ??

use pubs

create table answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

insert into answerint select '45299','7832.5'

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM answerint

drop table answerint

Works for me though !!!|||Crespo and a mriacle in the same post...holy cow!

Where have you been hiding out?

And Yes float is quirky...

But I would add

USE Northwind

CREATE TABLE answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

INSERT INTO answerint
SELECT '45299','7832.5' UNION ALL
SELECT 'BRETT', 'KAISER'

SELECT SUM(
(Cast(Qty1 as float))
+ (Cast(Qty2 as float))
) as intAnswer FROM answerint
WHERE ISNUMERIC(Qty1) = 1 AND ISNUMERIC(Qty2) = 1

DROP TABLE answerint|||Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.|||Originally posted by Crespo-n00b
Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.

Thanks for the replies.
I've tried it all out and they work for single select but not when a
"SUM" is used.

But I've found out that the answer came out the way it was because there were some NULL values in QTY2. When this happened, the result would be NULL even if QTY1 contained a figure.

Anymore ideas? I'm planning to manually grab these out evaluate/convert them using ASP before doing a calculation.

Thanks