Replacing column value on insert

Hello SQLServer-specialists!
I'm running a database which contains on table, where the customer is
allowed to import data into.
This table also has a column, which the customer should set to his name, so
that you know who imported the record.
The problem is, that the customer is not restricted in setting this columns
value, so he also could set it to 'foo'!
Now I want to ensure, that the value is always set to the logged on user,
when importing data.
I know, that using a view and an INSTEAD OF trigger would do the job.
But as the table has abount 250 columns, I try to find an easier way.
Is it possible to write a trigger, which executes just before the insert,
and is able to replace a columns value?
Thanks in advance!
MaxHi there,
why not setting up a UPDATE / INSERT trigger then modifying the users
name, like this one below which takes the SUSER_SNAME as a input, make
sure you modify that, to eventually reflect your enviroment:
CREATE TRIGGER SomeTrigger ON SOmeTable
FOR INSERT
AS
BEGIN
UPDATE SomeTable SET ModifiedColumn = SUSER_SNAME
FROM SomeTable S
INNER JOIN INSERTED I
ON S.PKColumn = I.PKColumn
END
HTH, Jens Suessmeyer,
http://www.sqlserver2005.de
--|||Markus,
You can use the system function SYSTEM_USER to get the current username.
You can create an AFTER Trigger for inserts and update the inserted rows
Username with the SYSTEM_USER function.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>|||Hello!
Thanks for your answers!
I now tried the following
CREATE TRIGGER ModificationTrigger ON TheTableToInsertTo
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO TheTableToInsertTo
SELECT col1, col2, suser_sname()
FROM inserted
END
First I thought, that inserting into the same table, as the trigger should
instead of an insert, could make problems, but it works perfectly!!!
Greetings,
Max
"Markus Emayr" <essmayr/at/racon-linz.at> schrieb im Newsbeitrag
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>

Replacing a NULL in a view

I have created a view over a table that extracts production
information. The undelying table contains rows not just for product
code changed but also for speed changes.
The folloing view fillers out the speed changes and seems to work bar
one small problem. The EndTime for the currently running product will
be NULL as it is still running. This is a proble as I miss the current
production information. Is there a way to make the EndTime NULL in the
MAX function if the table value is NULL or could NULL be replaced with
the current Datetime
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
Many thanks
JimYou can use ISNULL to provide a different value for one that is NULL.
for example
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, ISNULL(MAX(EndTime), GETDATE()) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jim" <jim.holmes@.devro-casings.com> wrote in message
news:68dfae14.0307230512.64cddbc6@.posting.google.com...
> I have created a view over a table that extracts production
> information. The undelying table contains rows not just for product
> code changed but also for speed changes.
> The folloing view fillers out the speed changes and seems to work bar
> one small problem. The EndTime for the currently running product will
> be NULL as it is still running. This is a proble as I miss the current
> production information. Is there a way to make the EndTime NULL in the
> MAX function if the table value is NULL or could NULL be replaced with
> the current Datetime
> SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
> HoursRun
> FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
> StartTime, MAX(EndTime) AS EndTime
> FROM D_ProductionLog
> GROUP BY Unit, Line, ProductCode) ProdLog
> ORDER BY StartTime, Unit, Line, ProductCode
> Many thanks
> Jim

replacing [XFO=BM] in a column

Hi,
I have a table that contains text columns with data containing some tags
like [XFO=BM] in it.
I'd like to remove these and am using a view that contains calls to a
function to do this, but it doesn't work if the tags have square brackets
surrounding them.
i.e. set @.working = replace (@.working, '[XFO=DN]', '')
doesn't work.
Does anyone know how I can do this, or how I need to delimit the []s to make
the tags vanish?
Thanks in advance,
IanYou need to escape the opening bracket:
set @.working = replace (@.working, '[[]XFO=DN]', '')
ML
http://milambda.blogspot.com/|||Not sure i've understood correctly as the following works for me on SQL2k
DECLARE @.WORKING VARCHAR(50)
SET @.wORKING = '[XFO=DN]'
SELECT replace (@.working, '[XFO=DN]', '')
Does the problem lie in the function ...? Are you using LIKE anywhere ...?
From Books Online
Symbol
Meaning
LIKE '[ [ ]' [
HTH. Ryan
"Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
> Hi,
> I have a table that contains text columns with data containing some tags
> like [XFO=BM] in it.
> I'd like to remove these and am using a view that contains calls to a
> function to do this, but it doesn't work if the tags have square brackets
> surrounding them.
> i.e. set @.working = replace (@.working, '[XFO=DN]', '')
> doesn't work.
> Does anyone know how I can do this, or how I need to delimit the []s to
> make
> the tags vanish?
> Thanks in advance,
> Ian|||
"ML" wrote:

> You need to escape the opening bracket:
> set @.working = replace (@.working, '[[]XFO=DN]', '')
Thanks for that, only unfortunately that didn't work. Delimiting both sets
of brackets didn't work either.
Any other ideas?
Ian|||Sorry, my bad. Ryan has a better answer. What was I thinking? No one knows.
ML
http://milambda.blogspot.com/|||
> Not sure i've understood correctly as the following works for me on SQL2k
> DECLARE @.WORKING VARCHAR(50)
> SET @.wORKING = '[XFO=DN]'
> SELECT replace (@.working, '[XFO=DN]', '')
> Does the problem lie in the function ...?
Hmmm, well the function although long is relatively straightforward...
ALTER FUNCTION convertPropertyTitle (@.inp text)
returns varchar (8000)
as
begin
if @.inp is null
return ' '
declare @.working varchar (8000)
set @.working = replace (cast (@.inp as varchar (8000)),
'{\rtf1\ansi\ansicpg1252\deff0\deflang10
33', '')
set @.working = replace (@.working, '{\rtf1\ansi\deff0{\fonttbl{\f0\fnil MS
Sans Serif;}}', '')
set @.working = replace (@.working,
'\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fprq
2\fcharset0 Times New Roman;', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;}}', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '{\f1\fnil\fcharset0 MS Sans Serif;}}', ''
)
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fcharset0 MS Sans
Serif;}{\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fprq2\fcharset0 Times
New Roman;', '')
set @.working = replace (@.working, '\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\f0\fs16', '<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang1033\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\f0\fs16',
'<u><b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\f0\fs16', '<u>')
set @.working = replace (@.working, '\viewkind4\uc1 d\i\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\i\f0\fs16', '<i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\i\f0\fs16',
'<u><b><i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\i\f0\fs16', '<b><i>
')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs24', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\f0\fs20', ''
)
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs20', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '}', '')
set @.working = replace (@.working, '{', '')
set @.working = replace (@.working, ' ', '')
set @.working = replace (@.working, '\ulnone', '</u>')
set @.working = replace (@.working, '\ul', '<u>')
set @.working = replace (@.working, '\lang2057\b\f1', '<b>')
set @.working = replace (@.working, '\lang1033\b\f0', '<b>')
set @.working = replace (@.working, '\lang1033', '')
set @.working = replace (@.working, '\lang2057', '')
set @.working = replace (@.working, '\lang2057\fs20', '')
set @.working = replace (@.working, '\b0', '</b>')
set @.working = replace (@.working, '\b', '<b>')
set @.working = replace (@.working, '\i0', '</i>')
set @.working = replace (@.working, '\i', '<i>')
set @.working = replace (@.working, 'SIGNED
\f1''85''85''85''85''85''85''85'
'85''85''85''85''85''85''85''
85''85', '')
set @.working = replace (@.working,
'DATE''85''85''85''85''85''85''85
''85''85''85''85''85''85''85\
''85''85''85', '')
set @.working = replace (@.working, '\f0', '')
set @.working = replace (@.working, '\f1', '')
set @.working = replace (@.working, '\fs20', '')
set @.working = replace (@.working, '\fs16', '')
set @.working = replace (@.working, char (10), '')
set @.working = replace (@.working, char (13), '')
set @.working = replace (@.working, '\tab', '')
set @.working = replace (@.working, '\super', '')
set @.working = replace (@.working, '\nosupersub', '')
set @.working = replace (@.working, '[WAITPHOTO]', '')
set @.working = replace (@.working, '''a3', '￡')
set @.working = replace (@.working, '\par', '<BR>')
set @.working = replace (@.working, '[[]XFO=DN]', '')
set @.working = replace (@.working, '[[]XFO=BN]', '')
set @.working = replace (@.working, '[[]XFO=CN]', '')
set @.working = replace (@.working, '[[]XFO=BM]', '')
set @.working = replace (@.working, '[[]XFO=CM]', '')
return @.working
end

> Are you using LIKE anywhere ...?
I then do a
ALTER view propertiesProperty
as
select reference, dbo. convertPropertyTitle(PropertyAccommodati
on)
PropertyAccommodation, dbo. convertPropertyTitle(propertyfulldetails
)
propertyfulldetails, dbo.convertPropertyTitle(propertysituation)
propertysituation, dbo.convertPropertyTitle(propertytitle) propertytitle
from properties
then reference it as
select * from propertiesproperty
where reference = 'xxx3333'
So no likes anywhere.
Thanks,
Ian

> From Books Online
> Symbol
> Meaning
> LIKE '[ [ ]' [
>
> --
> HTH. Ryan
> "Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
> news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
>
>

Replace values with a "*"

Hi, i have a report which contains a table with several columns. The
first column is filled with some data from a database, and can contain
data, that is some cells can be empty, some can have values...
So my question is, how can i make all the cells in that column that
HAVE a value, to have the value of "*".
Col1 Col1
-- --
1 *
5 HAS TO BE
8 *
4 *
Thnx!Use an IIF built-in function in an expression for the other columns textbox.
David
"ApeX" <jkdmaster_5@.hotmail.com> wrote in message
news:1186655248.283959.105240@.l70g2000hse.googlegroups.com...
> Hi, i have a report which contains a table with several columns. The
> first column is filled with some data from a database, and can contain
> data, that is some cells can be empty, some can have values...
> So my question is, how can i make all the cells in that column that
> HAVE a value, to have the value of "*".
> Col1 Col1
> -- --
> 1 *
> 5 HAS TO BE
> 8 *
> 4 *
> Thnx!
>|||put this in col2 field
=Iif(fields!Col1.value<>'', '*', fields!col1)
"ApeX" wrote:
> Hi, i have a report which contains a table with several columns. The
> first column is filled with some data from a database, and can contain
> data, that is some cells can be empty, some can have values...
> So my question is, how can i make all the cells in that column that
> HAVE a value, to have the value of "*".
> Col1 Col1
> -- --
> 1 *
> 5 HAS TO BE
> 8 *
> 4 *
> Thnx!
>

replace out of range dates

Hi,

I'm migrating data from access 2003 to sql server 2005. An access table contains dates like 5/1/0999. This is a date that is out of the datetime range from sql server.

Now my question is ... How do I replace these dates? I don't find something like IsDate in the expressions?

Ok

I did it with a Derived Column task.
It replaces the date column values who have out of range dates. (It only checks for dates smaller then 1/1/1753)

The expression is like this: [DateToCheck] < (DT_DATE)"1753-1-1" ? (DT_DATE)"1753-1-1" : [DateToCheck]

Replace Multiple Spaces with One Space?

I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task."Joe Cool" <joecool@.home.netwrote in message
news:5pb7t2dkt37mgh0cqnm3f9luuu1rvhglkm@.4ax.com...

Quote:

Originally Posted by

>I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task.

DECLARE @.FieldName varchar(5000)
SET @.FieldName = ' Reduce any number of' + SPACE(512) +
'spaces up to 512 in
a row to a single space
'

--You can reduce layers of REPLACE depending how many extra spaces you
expect.

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRI M(@.FieldName)),
SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '), SPACE(2), '
')|||On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@.hotmail.com>
wrote:

Quote:

Originally Posted by

>
>"Joe Cool" <joecool@.home.netwrote in message
>news:5pb7t2dkt37mgh0cqnm3f9luuu1rvhglkm@.4ax.com...

Quote:

Originally Posted by

>>I need to map several columns of data from one database to another
>where the data contains multiple spaces (once occurance of a variable
>number or spaces) that I need to replace with a single space. What
>would be the most efficient way to do this? I am using SQL2K. I was
>thinking a function since I know of no single Transact-SQL command
>that can accomplish this task.

>
>DECLARE @.FieldName varchar(5000)
>SET @.FieldName = ' Reduce any number of' + SPACE(512) +
>'spaces up to 512 in
>a row to a single space
>'
>
>--You can reduce layers of REPLACE depending how many extra spaces you
>expect.
>
>SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRI M(@.FieldName)),
>SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '), SPACE(2), '
>')
>

I believe you misunderstood my question. Here are some sample values:

"SMITH JR" (5 spaces)
"JONES JR" (7 spaces)

Desired result"

"SMITH JR"
"JONES JR"|||Joe Cool wrote:

Quote:

Originally Posted by

On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@.hotmail.com>
wrote:
>

Quote:

Originally Posted by

>"Joe Cool" <joecool@.home.netwrote in message
>news:5pb7t2dkt37mgh0cqnm3f9luuu1rvhglkm@.4ax.com...

Quote:

Originally Posted by

>>I need to map several columns of data from one database to another
>>where the data contains multiple spaces (once occurance of a variable
>>number or spaces) that I need to replace with a single space. What
>>would be the most efficient way to do this? I am using SQL2K. I was
>>thinking a function since I know of no single Transact-SQL command
>>that can accomplish this task.

>DECLARE @.FieldName varchar(5000)
>SET @.FieldName = ' Reduce any number of' + SPACE(512) +
>'spaces up to 512 in
>a row to a single space
>'
>>
>--You can reduce layers of REPLACE depending how many extra spaces you
>expect.
>>
>SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRI M(@.FieldName)),
>SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '), SPACE(2), '
>')
>>

>
I believe you misunderstood my question. Here are some sample values:
>
"SMITH JR" (5 spaces)
"JONES JR" (7 spaces)
>
Desired result"
>
"SMITH JR"
"JONES JR"

Looks to me like his answer produces the desired result. What problem
do you see with it?|||"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:45d51798$0$28101$4c368faf@.roadrunner.com...

Quote:

Originally Posted by

Joe Cool wrote:
>

Quote:

Originally Posted by

>On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@.hotmail.com>
>wrote:
>>

Quote:

Originally Posted by

>>"Joe Cool" <joecool@.home.netwrote in message
>>news:5pb7t2dkt37mgh0cqnm3f9luuu1rvhglkm@.4ax.com...
>>>I need to map several columns of data from one database to another
>>>where the data contains multiple spaces (once occurance of a variable
>>>number or spaces) that I need to replace with a single space. What
>>>would be the most efficient way to do this? I am using SQL2K. I was
>>>thinking a function since I know of no single Transact-SQL command
>>>that can accomplish this task.
>>DECLARE @.FieldName varchar(5000)
>>SET @.FieldName = ' Reduce any number of' + SPACE(512) +
>>'spaces up to 512 in a
>>row to a single space '
>>>
>>--You can reduce layers of REPLACE depending how many extra spaces you
>>expect.
>>>
>>SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRI M(@.FieldName)),
>>SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '),
>>SPACE(2), ' ')

>>
>I believe you misunderstood my question. Here are some sample values:
>>
>"SMITH JR" (5 spaces)
>"JONES JR" (7 spaces)
>>
>Desired result"
>>
>"SMITH JR"
>"JONES JR"

>
Looks to me like his answer produces the desired result. What problem
do you see with it?

It was a bit of overkill... maybe this will be closer to what you want.

CREATE FUNCTION dbo.SingleSpace(@.str varchar(8000))

RETURNS varchar(8000)

AS
--Usage
--SELECT dbo.SingleSpace('Function replaces any and all spaces up
to 16 in a row with a single space ')

BEGIN
DECLARE @.s varchar(8000)
SELECT @.s = REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(@.str)), SPACE(4), ' '),
SPACE(2), ' '), SPACE(2), ' ')
RETURN @.s
END|||Russ Rose wrote:

Quote:

Originally Posted by

"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:45d51798$0$28101$4c368faf@.roadrunner.com...

Quote:

Originally Posted by

>Joe Cool wrote:
>>

Quote:

Originally Posted by

>>On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@.hotmail.com>
>>wrote:
>>>
>>>"Joe Cool" <joecool@.home.netwrote in message
>>>news:5pb7t2dkt37mgh0cqnm3f9luuu1rvhglkm@.4ax.com...
>>>>I need to map several columns of data from one database to another
>>>>where the data contains multiple spaces (once occurance of a variable
>>>>number or spaces) that I need to replace with a single space. What
>>>>would be the most efficient way to do this? I am using SQL2K. I was
>>>>thinking a function since I know of no single Transact-SQL command
>>>>that can accomplish this task.
>>>DECLARE @.FieldName varchar(5000)
>>>SET @.FieldName = ' Reduce any number of' + SPACE(512) +
>>>'spaces up to 512 in a
>>>row to a single space '
>>>>
>>>--You can reduce layers of REPLACE depending how many extra spaces you
>>>expect.
>>>>
>>>SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRI M(@.FieldName)),
>>>SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '),
>>>SPACE(2), ' ')
>>I believe you misunderstood my question. Here are some sample values:
>>>
>>"SMITH JR" (5 spaces)
>>"JONES JR" (7 spaces)
>>>
>>Desired result"
>>>
>>"SMITH JR"
>>"JONES JR"

>Looks to me like his answer produces the desired result. What problem
>do you see with it?

>
It was a bit of overkill... maybe this will be closer to what you want.
>
>
CREATE FUNCTION dbo.SingleSpace(@.str varchar(8000))
>
RETURNS varchar(8000)
>
AS
--Usage
--SELECT dbo.SingleSpace('Function replaces any and all spaces up
to 16 in a row with a single space ')
>
BEGIN
DECLARE @.s varchar(8000)
SELECT @.s = REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(@.str)), SPACE(4), ' '),
SPACE(2), ' '), SPACE(2), ' ')
RETURN @.s
END

Doesn't work on 11, 14, or 15 spaces.

Adding an extra REPLACE(..., SPACE(2), ' ') wrapper will fix it, and
extend it to runs up to 26.

Adding an inner 8->1 on top of that extends to 167; 16->1 on top of that
extends to 2447; 32->1 on top of that extends to 77343, more than enough
unless you're messing with TEXT (for which REPLACE doesn't work at all,
IIRC) or VARCHAR(MAX).|||Do an UPDATE statement with nested REPLACE() functions to change (n)
spaces to 1 space:

UPDATE Foobar
SET mystring = REPLACE (SPACE(2), SPACE(1) ' ,
...
REPLACE (SPACE(<<fib(n)>>), SPACE(1),
mystring)
.. )));

The optimal pattern for the substitutions is a Fibbonnaci series with
the longest string of spaces in the innermost invocation. You have to
pick the right number based on the length of the column. Working out
the math is fun, so enjoy.|||"Russ Rose" <russrose@.hotmail.comwrote:

Quote:

Originally Posted by

>

How did you guys miss the recursion boat?

Create function dbo.udf_CondenseSpaces (@.str varchar(8000))

Returns varchar(8000)
AS

BEGIN
Declare @.s varchar(8000)

Set @.s = replace(@.str, ' ', ' ')

if charindex(' ', @.s) 0

set @.s = dbo.udf_CondenseSpaces(@.s)

return @.s
END|||also instead of calling a function many times
create a function or a proct
Sample incomming string

declare @.somestring varchar(100)
set @.somestring = 'aaa bbb cccc eee fff f gggg h i aa '
--sample guts of function / proc
while charindex(' ',@.somestring)>0 begin
set @.Somestring = replace(@.somestring,' ',' ')
end
print @.somestring
--output aaa bbb cccc eee fff f gggg h i aa

"Blackburn" <blackburn@.2centsediting.mypants.comwrote in message
news:1192727141_459@.sp12lax.superfeed.net...

Quote:

Originally Posted by

"Russ Rose" <russrose@.hotmail.comwrote:

Quote:

Originally Posted by

>>

>
>
How did you guys miss the recursion boat?
>
Create function dbo.udf_CondenseSpaces (@.str varchar(8000))
>
Returns varchar(8000)
AS
>
BEGIN
Declare @.s varchar(8000)
>
Set @.s = replace(@.str, ' ', ' ')
>
if charindex(' ', @.s) 0
>
set @.s = dbo.udf_CondenseSpaces(@.s)
>
return @.s
END

REPLACE Integers with Text Data

I am working with a database named “Documents” that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:

1 = Text

2 = Report

3 = Background

4 = Index

I would like to create a new column named “DocType” in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I).I was able to easily create the new column and cast the data type from integer to varchar:

--CREATE NEW COLUMN “DocType” WITH VARCHAR DATATYPE

ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL

Go

--UPDATE NEW COLUMN WITH CAST STRING

UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))

Go

But I have problems with the REPLACE method for replacing the numbers with letters.First I tried this based on the examples in MSDN Library:

--REPLACE NUMBERS WITH LETTERS

UPDATE Fulldocuments REPLACE (DocType,"1","T")

Which produced an error message: “Incorrect syntax near 'REPLACE'.”

Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:

UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")

Which produced the same error message: “Incorrect syntax near 'REPLACE'.”

I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.

Your UPDATE statement syntax is incorrect. And the 2nd syntax where you are trying to cast to DT_WSTR is not a valid SQL syntax also. See SQL Server Books Online for the complete UPDATE statement syntax and examples. UPDATE statement consists of SET, FROM and WHERE clauses. You are missing the SET clause. And you should use single-quotes preferably for string literals otherwise you will get errors most of the time depending on your SET options. Modify your update statement to:

UPDATE Fulldocuments SET DocType = REPLACE (DocType,'1','T')

But it doesn't seem like you want to create this DocType column in the first place. You should create a separate lookup table that contains the ids and description or type. You can then join with that table based on your ID value to get the description. This is much more flexible approach that doing it your way.

|||Thanks for correcting my syntax and the advice about creating a separate lookup table.

Replace Errors with NULL using Convert

I have a table with a varchar field that for the most part contains valid
dates ie (mm/dd/yyyy). There are some items that are not dates. I would
like a SQL statement that converts the varchar to a datetime and where there
is an error for a particular field will return a null for that field.
For example if my table contains the following items in Field1:
2/1/2005
1/1/2004
other data
3/1/2005
The query should return:
2005-2-1 00:00:00
2004-1-1 00:00:00
NULL
2005-3-1 00:00:00
If I use:
SELECT convert(datetime, Field1) as Field1
FROM table1
Then I get a conver error. Any ideas or suggestions would be helpful.
Thanks!
Dan,
See if this works:
select
case when ISDATE(Field1) = 1
then cast(Field1 as datetime)
else NULL end
from table1
If you can't live with the fact that some garbled
data may convert unexpectedly (the string '110919'
will convert to September 19, 2011, for example),
you'll have to do some pattern matching of your own
as well, such as
case when Field1 like '%/%/%' and Field1 not like '%/%/%/%' ...
and isdate(Field1) = 1 then ...
Steve Kass
Drew University
Dan wrote:

>I have a table with a varchar field that for the most part contains valid
>dates ie (mm/dd/yyyy). There are some items that are not dates. I would
>like a SQL statement that converts the varchar to a datetime and where there
>is an error for a particular field will return a null for that field.
>For example if my table contains the following items in Field1:
>2/1/2005
>1/1/2004
>other data
>3/1/2005
>The query should return:
>2005-2-1 00:00:00
>2004-1-1 00:00:00
>NULL
>2005-3-1 00:00:00
>If I use:
>SELECT convert(datetime, Field1) as Field1
>FROM table1
>Then I get a conver error. Any ideas or suggestions would be helpful.
>Thanks!
>
>

Replace carriage return and line feed in a Text column?

Hi,

I've a text column (text datatype) that contains carriage return and line feed.

Syntax-wise, how can I replace these by a space?

Thanks.Hi,

I just got the answer. Thanks.

http://www.winnetmag.com/SQLServer/Article/ArticleID/20699/20699.html

SELECT
TextId,
replace ( replace(TextValue, char(10),
''), char(13), '') ModifiedTextValue
FROM
BadTextData

Repeating Group headers on reports with subreports

I have a report that contains a table. The group header in the table is set
to repeat on a new page. However, as soon as I place a subreport in the table
of the main report and that subreport causes a page break...the group header
on the top-level report doesn't repeat. Is there a workaround for this
shortcoming?
Regards,
Dean NeroNero,
Did you ever find an answer for this? I am having the same issue...and
its killin' me!
Michael
"Dean Nero" wrote:
> I have a report that contains a table. The group header in the table is set
> to repeat on a new page. However, as soon as I place a subreport in the table
> of the main report and that subreport causes a page break...the group header
> on the top-level report doesn't repeat. Is there a workaround for this
> shortcoming?
> Regards,
> Dean Nero

Repeated Elements

Can someone please help? I need the tags sorted by record
(which contains many levels of elements), not by a group
of <LName>elements.
Using
http://localhost/Customer/views/Empl...xsd/Employee/L
Name='King' I get the following:
<LName>Buchanan</LName><LName>Callahan</LName><LName>Davoli
o</LName><LName>Dodsworth</LName><LName>Fuller</LName><LNam
e>King</LName><LName>Leverling</LName><LName>Peacock</LName
><LName>Suyama</LName>
--^
I am not sure what the problem is. Do you want a single wrapper element
around? Do you want a different result? How would that look like?
Thanks
Michael
"TO" <anonymous@.discussions.microsoft.com> wrote in message
news:2e48a01c46a9d$fe009fc0$a301280a@.phx.gbl...
> Can someone please help? I need the tags sorted by record
> (which contains many levels of elements), not by a group
> of <LName>elements.
> Using
> http://localhost/Customer/views/Empl...xsd/Employee/L
> Name='King' I get the following:
> <LName>Buchanan</LName><LName>Callahan</LName><LName>Davoli
> o</LName><LName>Dodsworth</LName><LName>Fuller</LName><LNam
> e>King</LName><LName>Leverling</LName><LName>Peacock</LName
> --^
>

Rendering problem in browser

I have a basic report with 3 text boxes. The first text box contains a title
and two smaller text boxes. The two smaller textboxes contain a string
"Report Created:" and the date and time the report was created. The layout in
VS is correct, it is correct in the preview within in VS. When I export the
report to Adobe, Excel, etc. it is correct. However, when I deploy the report
it is not correct. The two smaller text boxes are below the main text box.
Below is an example of how I want it to look and how it looks in preview,
Adobe, Excel, etc..
|----|
| Report Title
|
|
|
|
|
| -- --
|
||Report Created:||Date and Time| |
| -- --
|
|
|
|----|
Table data
And this is how it looks in a browser:
|----|
| Report Title
|
|
|
|
|
|
|
|
|
|
|
|
|
|----|
|Report Created:|
|Date and Time|
Table data
Does anyone have any thoughts about what is going on or any suggestions on
how to correct this. I have deleted and recreated the report several times, I
have changed the properties on all the text boxes (grow, shrink, etc.) and no
luck.
Thanks.On Tue, 25 Jan 2005 09:15:04 -0800, "MAGrimsley"
<MAGrimsley@.discussions.microsoft.com> wrote:
>I have a basic report with 3 text boxes. The first text box contains a title
>and two smaller text boxes. The two smaller textboxes contain a string
>"Report Created:" and the date and time the report was created. The layout in
>VS is correct, it is correct in the preview within in VS. When I export the
>report to Adobe, Excel, etc. it is correct. However, when I deploy the report
>it is not correct. The two smaller text boxes are below the main text box.
>Below is an example of how I want it to look and how it looks in preview,
>Adobe, Excel, etc..
>|----|
>| Report Title
> |
>|
> |
>|
> |
>| -- --
> |
>||Report Created:||Date and Time| |
>| -- --
> |
>|
> |
>|----|
>Table data
>And this is how it looks in a browser:
>|----|
>| Report Title
> |
>|
> |
>|
> |
>|
> |
>|
> |
>|
> |
>|
> |
>|----|
>|Report Created:|
> |Date and Time|
>Table data
>Does anyone have any thoughts about what is going on or any suggestions on
>how to correct this. I have deleted and recreated the report several times, I
>have changed the properties on all the text boxes (grow, shrink, etc.) and no
>luck.
>Thanks.
Your textboxes could be too close to each other. Either move them
apart, or use the header row(s) in your table instead. You can also
consolidate these 2 textboxes into 1 and set its value to:
= "Report created: " & Now()|||Thanks for your help, I combinded both textboxes to one and it stil shows up
under the main textbox.
"Usenet User" wrote:
> On Tue, 25 Jan 2005 09:15:04 -0800, "MAGrimsley"
> <MAGrimsley@.discussions.microsoft.com> wrote:
> >I have a basic report with 3 text boxes. The first text box contains a title
> >and two smaller text boxes. The two smaller textboxes contain a string
> >"Report Created:" and the date and time the report was created. The layout in
> >VS is correct, it is correct in the preview within in VS. When I export the
> >report to Adobe, Excel, etc. it is correct. However, when I deploy the report
> >it is not correct. The two smaller text boxes are below the main text box.
> >
> >Below is an example of how I want it to look and how it looks in preview,
> >Adobe, Excel, etc..
> >
> >|----|
> >| Report Title
> > |
> >|
> > |
> >|
> > |
> >| -- --
> > |
> >||Report Created:||Date and Time| |
> >| -- --
> > |
> >|
> > |
> >|----|
> >
> >Table data
> >
> >And this is how it looks in a browser:
> >
> >|----|
> >| Report Title
> > |
> >|
> > |
> >|
> > |
> >|
> > |
> >|
> > |
> >|
> > |
> >|
> > |
> >|----|
> >
> >|Report Created:|
> > |Date and Time|
> >
> >Table data
> >
> >Does anyone have any thoughts about what is going on or any suggestions on
> >how to correct this. I have deleted and recreated the report several times, I
> >have changed the properties on all the text boxes (grow, shrink, etc.) and no
> >luck.
> >
> >Thanks.
>
> Your textboxes could be too close to each other. Either move them
> apart, or use the header row(s) in your table instead. You can also
> consolidate these 2 textboxes into 1 and set its value to:
> = "Report created: " & Now()
>|||On Tue, 25 Jan 2005 10:53:04 -0800, "MAGrimsley"
<MAGrimsley@.discussions.microsoft.com> wrote:
>Thanks for your help, I combinded both textboxes to one and it stil shows up
>under the main textbox.
>
Hmm, the example you provided showed Report Title on top and 2 smaller
textboxes underneath (i.e. in 2 rows) while after deployment you were
getting them in 3 rows...
Anyway, if spreading textboxes apart doesn't help (also make sure they
do not overlap), use a table instead - this is always a much cleaner
solution, no surprises. I stopped using textboxes a long time ago for
this very reason (flawed positioning), and only use them for
well-isolated fields and labels. Tables can have multiple header and
footer rows, plus you can have separate tables consisting only of
header and/or footer rows, which you can use to substitute report
headers/footers and page headers/footers.
>"Usenet User" wrote:
>> On Tue, 25 Jan 2005 09:15:04 -0800, "MAGrimsley"
>> <MAGrimsley@.discussions.microsoft.com> wrote:
>> >I have a basic report with 3 text boxes. The first text box contains a title
>> >and two smaller text boxes. The two smaller textboxes contain a string
>> >"Report Created:" and the date and time the report was created. The layout in
>> >VS is correct, it is correct in the preview within in VS. When I export the
>> >report to Adobe, Excel, etc. it is correct. However, when I deploy the report
>> >it is not correct. The two smaller text boxes are below the main text box.
>> >
>> >Below is an example of how I want it to look and how it looks in preview,
>> >Adobe, Excel, etc..
>> >
>> >|----|
>> >| Report Title
>> > |
>> >|
>> > |
>> >|
>> > |
>> >| -- --
>> > |
>> >||Report Created:||Date and Time| |
>> >| -- --
>> > |
>> >|
>> > |
>> >|----|
>> >
>> >Table data
>> >
>> >And this is how it looks in a browser:
>> >
>> >|----|
>> >| Report Title
>> > |
>> >|
>> > |
>> >|
>> > |
>> >|
>> > |
>> >|
>> > |
>> >|
>> > |
>> >|
>> > |
>> >|----|
>> >
>> >|Report Created:|
>> > |Date and Time|
>> >
>> >Table data
>> >
>> >Does anyone have any thoughts about what is going on or any suggestions on
>> >how to correct this. I have deleted and recreated the report several times, I
>> >have changed the properties on all the text boxes (grow, shrink, etc.) and no
>> >luck.
>> >
>> >Thanks.
>>
>> Your textboxes could be too close to each other. Either move them
>> apart, or use the header row(s) in your table instead. You can also
>> consolidate these 2 textboxes into 1 and set its value to:
>> = "Report created: " & Now()
>>