HI
I hv a table that contains a column which contains unformated data .
Like
Col1 varchar(35) col1 varchar(35)
AS PD FT AS-PD-FT
AS-PD GH AS-PD-GH
R GT-TY R-GT-TY
S G- S-G
i hv 2 update d table. any idea?
Sameer

give some more detail on what u reqd.

Before After
Col1 varchar(35) col1 varchar(35)
AS PD FT AS-PD-FT
AS-PD GH AS-PD-GH
R GT-TY R-GT-TY
S G- S-G
i want each word in this column should be separated with "-". This
table contains around 90,000 records and i hv to modify that column only

Assuming you're running ASE 12.5.0.3+ you could use the str_replace()
function to replace blanks with '-':

update <table_name>
setCol1 = str_replace(Col1,' ','-')

This assumes that Col1 values do not start with blanks.

This update will not solve the problem with the last example where Col1
currently ends with a '-'. This could be fixed with some additional code
either an additional 'update' or a more complicated change to the above
'update'.

You really need to provide a complete set of specs for what the Col1 data
currently looks like leading/trailing spaces? need to remove
leading/trailing '-'s? need to remove any other leading/trailing
characters other than space or '-'?

Thakur wrote:

Before After
Col1 varchar(35) col1 varchar(35)

AS PD FT AS-PD-FT
AS-PD GH AS-PD-GH
R GT-TY R-GT-TY
S G- S-G

i want each word in this column should be separated with "-". This
table contains around 90,000 records and i hv to modify that column only

Also
- does this have to be done as 1 'update' or can multiple 'update's be used?
- is this a one time thing or do you have to constantly issue this update?
(it may be of benefit to add some code to the client and/or a trigger to
make sure the data is formatted 'properly' at the time it's entered into
the database)

Mark A. Parsons wrote:

Assuming you're running ASE 12.5.0.3+ you could use the str_replace()
function to replace blanks with '-':

update <table_name>
set Col1 = str_replace(Col1,' ','-')
--
This assumes that Col1 values do not start with blanks.

This update will not solve the problem with the last example where Col1
currently ends with a '-'. This could be fixed with some additional
code either an additional 'update' or a more complicated change to
the above 'update'.

You really need to provide a complete set of specs for what the Col1
data currently looks like leading/trailing spaces? need to remove
leading/trailing '-'s? need to remove any other leading/trailing
characters other than space or '-'?

Thakur wrote:
>
>Before After
>Col1 varchar(35) col1 varchar(35)
>
>
>AS PD FT AS-PD-FT
>AS-PD GH AS-PD-GH
>R GT-TY R-GT-TY
>S G- S-G
>>
>i want each word in this column should be separated with "-". This
>table contains around 90,000 records and i hv to modify that column only
>>