Solved

SQL Server/DTS: text field to char field

Posted on 2008-10-22
2
317 Views
Last Modified: 2013-11-30
I'm accessing an existing SQL 2005 table that has a TEXT field with length of 2147483647. The data in the field is character-delimited.  (If I want to find the Date of Service, I'd have to look for "DATEOFSERVICE" and date would appear shortly after that.)

Is there a way to do this?  

Does the TEXT field need to be convert to char?  If so, how?

Thanks, Ashley
0
Comment
Question by:BCAshleyP
2 Comments
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 22780716
Text fields cannot be manipulated like a varchar field.  So you would have to convert it to varchar(4000) or whatever length is long enough.
In SQL 2005 you should use varchar(max) instead of text because then you can just do a charindex() instead of conversion and charindex().

CONVERT(varchar(4000), textfield1)

Does that help?
0
 

Author Comment

by:BCAshleyP
ID: 22781673
Thanks!  For some reason I couldn't get it to work (operator error I'm sure), but this led me to the obvious: I copied the data into a new table (I don't own the original table), but defined the text field X as varchar(max) in the new table.  This seems to work.

THANKS again for your help!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now