Q
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):

 MC68060RC50

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.

This was last published in January 2005

Dig Deeper on SQL Server Database Modeling and Design

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close