Separating one field into multiple fields

Separating one field into multiple fields

Post by Michae » Thu, 11 Dec 2003 03:18:11



I have a field in a table which contains a string including Carriage
Returns, can anyone tell me how I could separate this field into separate
fields based on where the carriage returns are using a simple SQL command.

Thanks

Michael

 
 
 

Separating one field into multiple fields

Post by Laure » Fri, 12 Dec 2003 00:44:27


Check out the CHARINDEX, CHAR, and SUBSTRING functions.  
By nesting these creatively, you should be able to lift
out the sections you want based on where the carriage
returns (CHAR(13)) are found.  You could then take the
isolated data and output it as a separate field or set to
a variable.  You may need a variable or two to hold the
positions for where the CRs show up so you can "march
through" the entire string to get all the fields (adjust
the CHARINDEX start position past the last one you just
found).

Quote:>-----Original Message-----
>I have a field in a table which contains a string
including Carriage
>Returns, can anyone tell me how I could separate this
field into separate
>fields based on where the carriage returns are using a
simple SQL command.

>Thanks

>Michael

>.


 
 
 

1. Combining multiple records into one coma-separated field

I have one table that holds physicians and another that
holds their specialties.  There can be multiple
specialties for each physician.  I need to combine all of
the specialties for that physician into a coma-separated
field that will show up in a provider directory.  My
tables are setup similar to the following:

Physician table:
Physician_ID           Physician
   1                      Dr. Smith
   2                      Dr. Jones

Specialty Table:
Physician_ID           Specialty
   1                      General Surgery
   1                      OB/GYN
   2                      Pediatrics
   2                      Neonatology

I need the output to look like the following:

Physician                 Specialty
    Dr. Smith                General Surgery, OB/GYN
    Dr. Jones                Pediatrics, Neonatology

It is unlikely that a physician will have more than 3 or
four specialties so I should be able to store the results
in a varchar(255) field.  

2. Remote database

3. extracting address text from one field into separate fields

4. sybase for sco and linux

5. mysql - separate many values in one field separated by pipe

6. Visual SQL

7. One-field INDEX performance VS multiple fields index

8. Gupta SQLWindows / SQLServer connectivity problems

9. Distinct on only one field of a multiple field select

10. web one entry for multiple field search??multiple

11. separating checkbox to multiple fields

12. insert data from one field in one table to another field in another table

13. separate a name into 2 separate fields