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?

How can i change collation of userdefined datatype?

Hi everyone?

How can i change collation of userdefined datatype?
When i try to change it Management Studio Query

varcharUzun = varchar(100) --> userdefined data type....

[code]ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL [/code]

when i run the query it says.
[code]
Msg 452, Level 16, State 1, Line 2
COLLATE clause cannot be used on user-defined data types.
[/code]

i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey
Is there any other to change the collation without dropping and re-creating the objects.

Thanks.

Hi Cem,

I don't htink you can set collation for a column that is using USER DEFINED DATATYPE.

If you do need to use the collation, just use the system datatype varchar(100), instead of user-defined, on that column.

regards

Jag

|||

Thanks Jag

but i have to use user defined datatypes.

No way to change the COLLATION of TYPES..... i couldnt find any ALTER TYPE xxxx COLLATE = xxx :(

And also how can i change the collation of PrimaryKey (varchar(10))....

2 Main problems:(

cemuney

|||

Hi Cem,

I dont think you can change the collation for user defined data type.

To change collation for a particular column try the following:

ALTER TABLE <tableName>

ALTER COLUMN <columnName> VARCHAR(10) COLLATE <collationName>

Let me know how you got on.

regards

Jag

|||

Hi Jag

i understant that UDDType do not have collations itself.

it is coming from the DATABASE's default collation.

When i change the COLLATION of database, also UDDType's collation also changes.

thanks anyway.

How can i change collation of userdefined datatype?

Hi everyone?

How can i change collation of userdefined datatype?
When i try to change it Management Studio Query

varcharUzun = varchar(100) --> userdefined data type....

[code]ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL [/code]

when i run the query it says.
[code]
Msg 452, Level 16, State 1, Line 2
COLLATE clause cannot be used on user-defined data types.
[/code]

i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey
Is there any other to change the collation without dropping and re-creating the objects.

Thanks.

Hi Cem,

I don't htink you can set collation for a column that is using USER DEFINED DATATYPE.

If you do need to use the collation, just use the system datatype varchar(100), instead of user-defined, on that column.

regards

Jag

|||

Thanks Jag

but i have to use user defined datatypes.

No way to change the COLLATION of TYPES..... i couldnt find any ALTER TYPE xxxx COLLATE = xxx :(

And also how can i change the collation of PrimaryKey (varchar(10))....

2 Main problems:(

cemuney

|||

Hi Cem,

I dont think you can change the collation for user defined data type.

To change collation for a particular column try the following:

ALTER TABLE <tableName>

ALTER COLUMN <columnName> VARCHAR(10) COLLATE <collationName>

Let me know how you got on.

regards

Jag

|||

Hi Jag

i understant that UDDType do not have collations itself.

it is coming from the DATABASE's default collation.

When i change the COLLATION of database, also UDDType's collation also changes.

thanks anyway.

sql