January 19th, 2004, 11:02 PM
is there a quick way to do this within sql.
i am thinking of something along the lines of what would be an equivalent to something in PHP (obviously not a rdbms) like
foreach ($ArrayName as $varName=>$value)
$value = rtrim(ltrim($value));
instead of having to do each variable manually.
Thanks for the help
January 20th, 2004, 12:34 AM
One reason this will not work is because TSQL variables are strong typed. You would error out if you attempted this with a date or number variable.
One option is to create a function that contains all the variables called resetVariables that does nothing but reset the variables.
January 20th, 2004, 12:42 AM
thanks for the help. I was hoping there was a quick way.
January 20th, 2004, 02:13 AM
Another reason is MS-SQL has no concept of arrays. You would have to use cursors and it would be slow and ugly, and like victor pointed out you would run into issues with strongly typed columns.
If you are using varchar fields they will be automatically trimmed. If you are using char fields they will contain leading and trailing spaces.
If you are storing variable width data use a varchar and trim it on the way in through a sproc. Then you do not need to trim them on the way out. Otherwise just trim the columns on the way out in a select..
You can also update a table and trim the values, however this will do no good if they are stored type char.
select ltrim(rtrim( nme ) ) from dbo.fooBar where id = 1
create table fooBar(
id int identity(1,1) primary key,
stat varchar(10) check (stat IN('Retired', 'Playing', 'Injured') ),
create procedure insert_foobar( @stat varchar(10), @nme varchar(10), @foo int )
insert into dbo.fooBar ( stat, nme, foo ) values( ltrim(rtrim(@stat)), ltrim(rtrim(@nme)), @foo )
Last edited by abombss; January 20th, 2004 at 02:20 AM.
January 20th, 2004, 09:08 AM
I might have misunerstood your question. You can do a mass update to all the rows in a table with an update query.
This will trim all the rows in the column named stat in the fooBar table.Careful... If you do not use a where clause you could update some data you don't want to.The following example uses a where clause to limit the update to rows with a col1 value of 3.
update dbo.fooBar set stat = ltrim(rtrim(stat))
update dbo.fooBar set col2 = ltrim(rtrim(col2)), col3 = upper(col3)
where col1 = 3