Problem solve Get help with specific problems with your technologies, process and projects.

Split a column value based on numeric character

I have field that has the following type of info (it is VARCHAR):


I want to split the data into "prefix" and "basenumber" so that everything to the left of the first numeric value would be inserted into the "prefix" field and the rest would go into the "basenumber" field. The results should look like this (2 seperate fields):

 prefix basenumber MC 68060RC50

How can I do this? I am using SQL 2000.

Use the PATINDEX function to find the position of the first numeric. Then use the LEFT and SUBSTRING functions to split the field at that point.

 select case when patindex('%[0-9]%',info) > 0 then left(info,patindex('%[0-9]%',info)-1) else info end as prefix , case when patindex('%[0-9]%',info) > 0 then substring(info,patindex('%[0-9]%',info) , len(info)-patindex('%[0-9]%',info)+1) else null end as basenumber from splittable

Easy when you see it, not so easy to come up with the first time, eh.

FYI, here is the data used to test the above query:

 insert into splittable(info) values('MC68060RC50') insert into splittable(info) values('AAAAAAAAAAAAAAA444') insert into splittable(info) values('NO NUMERIC!') insert into splittable(info) values('937 leading numeric')

It is important when writing complex SQL like this to test it on a number of values that represent various types of conditions.

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.