Solved

SQL Server/DTS: text field to char field

Posted on 2008-10-22
2
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

739 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