How can I change the wrong inderted data

I wish someone can help in this.

One column in the table is varchar(64), normally it should contain data in the form
xxxxxxx/yyyyyyy
where xxxxx and yyyyyyy are numbers

A new software is inserting the data with following format
xxxxxxx/yyyyyyy/xxxxxxx/yyyyyyy

This causing problem for the program to read this data and it will be hard to change its code.

What can be the best way to control that from within the SQL?

All helps would be appreciatedStep 1: Fire software vendor that updated the code in a stupid way.

Here is one way to extract the data. You should be able to modify this a littel to get the removal of bad data.

create table test1
(id int identity(1, 1),
col1 varchar(20))

insert into test1 (col1) values ('123/456/789/000')
insert into test1 (col1) values ('123/456/000/789')
insert into test1 (col1) values ('123456789/456/7/000')
insert into test1 (col1) values ('123/456')

select substring (col1, charindex ('/', col1, charindex ('/', col1) + 1)+ 1, 20)
from test1
where col1 like '%/%/%/%'|||I came up with:

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 varchar(50))
GO

INSERT INTO myTable99 (Col2)
SELECT '1111111/2222222' UNION ALL
SELECT '3333333/4444444' UNION ALL
SELECT '5555555/6666666' UNION ALL
SELECT '7777777/8888888/9999999/0000000'
GO


SELECT Col2
FROM myTable99
WHERE LEN(Col2) = LEN(REPLACE(Col2,'/','')) + 1
UNION ALL
SELECT SUBSTRING(Col2,1,LEN(Col2)-CHARINDEX('/',Col2,(CHARINDEX('/',Col2)+1)))
FROM myTable99
WHERE LEN(Col2) = LEN(REPLACE(Col2,'/','')) + 3
UNION ALL
SELECT SUBSTRING(Col2,LEN(Col2)-CHARINDEX('/',Col2,(CHARINDEX('/',Col2)+1))+2,
LEN(Col2)-(LEN(Col2)-CHARINDEX('/',Col2,(CHARINDEX('/',Col2)+1))+1))
FROM myTable99
WHERE LEN(Col2) = LEN(REPLACE(Col2,'/','')) + 3
GO

DROP TABLE myTable99
GO|||What do you mean by "control"? Do you mean fix the existing data, or do you want to prevent more bad data from being entered?

And what do you want to do with the bad data? You can exclude the entire row, or accept only the valid characters, or (more complicated) split the value into two records?

blindman|||The bad data will keep coming always, I just want to put somthing to reshape the data and enter them in correct way

thanks for all

Originally posted by blindman
What do you mean by "control"? Do you mean fix the existing data, or do you want to prevent more bad data from being entered?

And what do you want to do with the bad data? You can exclude the entire row, or accept only the valid characters, or (more complicated) split the value into two records?

blindman|||Thanks all for the help, regards,|||What you need is a trigger on your table that automatically verfies and modifies the data as it is entered, using logic like that suggested by brett and MCrowley.

Do you know how to write a trigger?

blindman|||Well, if the data is required to be in that format, then you can mess with them...

CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 varchar(64) CHECK (LEN(COL2)-LEN(REPLACE(Col2,'/','')) = 1))
GO

INSERT INTO myTable99 (Col2)
SELECT '1111111/2222222' UNION ALL
SELECT '3333333/4444444' UNION ALL
SELECT '5555555/6666666'
GO

INSERT INTO myTable99 (Col2)
SELECT '7777777/8888888/9999999/0000000'
GO

SELECT Col2
FROM myTable99
GO

DROP TABLE myTable99
GO

I would stay away from the trigger...

But you may not have a choice, but to use one...

What is this table used for?

EDIT: How does the data get in? OLTP or Loads?