I have field that has the following type of info (it is VARCHAR):
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.