SEO

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>
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
>

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
>>