problem in field data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem in field data

Author

Topic

amarsas
Starting Member

11 Posts

Posted - 2002-11-14 : 12:30:25

my table has almost 200 fields.
any field may have a value for example field height may have a value 5'10"
i want to return the value as 5'10 i.e. remove the quotes. i need to be able to do this with all the fields and that too using a SELECT statement.
for example:
select replace(all fields,'"','') from table
how do i do this??

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-14 : 12:35:34

you can generate the statements by

select 'replace(' + name + ',''"'',''''),'
from syscolumns where id = object_id('tblname')

copy this into your select statement.

If you want to do it without knowing the table structure then you will have to use dynamic sql to build the statement.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

amarsas
Starting Member

11 Posts

Posted - 2002-11-14 : 12:50:06

Thank you nr. Could you explain a bit more when you say that i need to use dynamic sql(sp_executesql).

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-14 : 13:02:24

something like

declare @sql varchar(8000)

select @sql = coalesce(@sql + ',','') + 'replace(' + name + ',''"'','''')'
from syscolumns where id = object_id('tblname')

select @sql = 'select ' + @sql + ' from tblname'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

amarsas
Starting Member

11 Posts

Posted - 2002-11-14 : 13:13:23

Thank you nr. I really appreciate your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources