Solved

SQL Server/DTS: text field to char field

Posted on 2008-10-22
2
331 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

622 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