Splitting 1 column into 2 based on spaces

Question

Post reply

Splitting 1 column into 2 based on spaces

imjhoskins

SSC Rookie

Points: 35
More actions
July 22, 2005 at 7:10 am

#165020

I have records in a table that I need to split into 2 columns during a conversion. I need to strip off the code at the end of the sample lines below to populate a new column and the remaining data will go to another column. The problem is the code at the end is not in the same place or the same length for each record, although always at the end, and the number of spaces between the name and code can vary and the length of the name itself can vary. The field type is varchar(120) before the conversion.
Basically, I have a code at the end which the majority can be picked up by left(field,6) but no good way to cut the code off the end to populate the other column.
Anyone have suggestions on how to split the record?
COMPUTER CORP 45474
SQL IMAGING, INC 90159S
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Try running this code and you'll get the idea:

declare @a varchar(120), @b-2 varchar(120)

set @a = 'COMPUTER CORP 45474'

set @b-2 = 'SQL IMAGING, INC 90159S'

select reverse(left(reverse(@a), charindex(' ', reverse(@a))-1))

select rtrim(left(@a, len(@a) - len(reverse(left(reverse(@a), charindex(' ', reverse(@a))-1)))))

select reverse(left(reverse(@b), charindex(' ', reverse(@b))-1))

select rtrim(left(@b, len(@b) - len(reverse(left(reverse(@b), charindex(' ', reverse(@b))-1)))))

Regards

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

noeld SSC Guru Points: 96590 More actions · Answer 2

OR

declare @a varchar(120), @b-2 varchar(120)

set @a = 'COMPUTER CORP 45474'

set @b-2 = 'SQL IMAGING, INC 90159S'

select left(@a, charindex(' ', @a)-2), right(@a, charindex(' ', reverse(@a))-2) -- two spaces

select left(@b, charindex(' ', @b-2)-2), right(@b, charindex(' ', reverse(@b))-2) -- two spaces

* Noel

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 3

or if u use ADO.NET to connect to yr server and do some data processing, u can use this class to split strings according to Regular Expression pattern:

using

System;

using

System.Text.RegularExpressions;

public

class SplitTest

{

public static void Main()

{

// Split on multiple spaces ; match prefix but exclude it (?<=)

Regex r =

new Regex( @"(?<=\w)(\s){2,}" );

string[] mystring = r.Split("COMPUTER CORP 45474");

foreach (string s in mystring)

{

if (s.Length > 0 )

Console.WriteLine(s);

}

Console.WriteLine("\nPress Enter to Exit");

Console.ReadLine();

/* returns two strings:

COMPUTER CORP

45474

*/

}

//end of Main

}

//end of class

Ray M SSC-Insane Points: 21093 More actions · Answer 4

Woo, Can't wait til I can use regX in a function in SQL 2k5