If the records already exists in a database, you will need to run a stored
procedure to scrub and clean your data. Since you are running SQL Server
2000, you can use table variables to perform this very quickly.
Example:
declare
@firstName varchar(50)
, @maxRows int
, @currentRow int
select
@currentRow = 1
declare @dataSet table
(
rowID int identity (1, 1) primary key not null
, pkID int
, firstName varchar(50) not null
)
insert into
@dataSet
select
firstName
from
tblFoo
select
@maxRows = count(rowID)
from
@dataSet
while @currentRow <= @maxRows
begin
select
@firstName = firstName
, @pkID = pkID
from
@dataSet
where
rowID = @currentRow
select @firstName = LTrim(RTrim(@firstName))
update
tblFoo
set
firstName = @firstName
where
pkID = @pkID
select @currentRow = @currentRow + 1
end
Kind of like that. You can scrub full tables at a time by adding more
columns to the table variable.
Teddy
On 12/22/06, Douglas Knudsen <[EMAIL PROTECTED]> wrote:
not sure of the SLQ Server stuff, but in the CF code below you can
utilise: Trim(Replace(myString,' ','')). Actually, looks like the code is
already using Trim(), just need to add the Replace() as above to remove
intermediate spaces.
str_Token = Replace(trim(mid(arguments.str_Content, int_CurrPos,
int_NextPos - int_CurrPos)),' ','', 'All');
DK
On 12/21/06, Ajas Mohammed <[EMAIL PROTECTED]> wrote:
>
> Hello everyone,
> I need help on this one real quick. One of my colleauges used this
> custom CF tag which parses a tab delimited text file and stores in array.
> How do I add functionality to this code so that it would trim all the spaces
> i.e either left or right or if value sent in txt file has spaces?????
>
> If you understand the code , that will be great and if you could
> pinpoint me to what needs to be done, then nothing like it... Also any
> explanation of this code will help a lot.
>
> I tried ltrim(rtrim(colvalue)) while inserting records but it doesnt
> work in sql server 2000. Once records are inserted in table, I am not able
> to trim the records.. If you know any sql server 2000 fix for removing extra
> spaces left or right or remove extra spaces for values which are sent with
> empty tabs or spaces???
>
> Thanks in advance..if u cant open attachment , here is the code...
>
> <cfparam name="attributes.str_Filename" default="" />
> <cfparam name="attributes.str_LineDelimiter"
> default="#chr(13)##chr(10)#" />
> <cfparam name="attributes.str_TokenDelimiter" default="#chr(9)#">
>
> <cffunction name="TokenizeLine" returntype="array">
> <cfargument name="str_Content" type="string" required="true" />
> <cfargument name="str_Delimiter" type="string" required="true" />
>
> <cfset var ar_Tokens = arrayNew(1) />
> <cfset var int_CurrPos = 1 />
> <cfset var int_NextPos = 1 />
> <cfset var str_Token = "" />
>
> <cfscript>
> int_NextPos = REFind("#arguments.str_Delimiter#|$",
> arguments.str_Content, int_CurrPos);
> while (int_NextPos gt 0)
> {
> str_Token = trim(mid(arguments.str_Content, int_CurrPos,
> int_NextPos - int_CurrPos));
> arrayAppend(ar_Tokens, str_Token);
> int_CurrPos = int_NextPos + len(arguments.str_Delimiter );
> int_NextPos = REFind("#arguments.str_Delimiter#|$",
> arguments.str_Content, int_CurrPos);
> }
>
> // If the line ends with a token, add
> // an extra empty element to the array
> if (len(arguments.str_Content) gt 0 and mid(
> arguments.str_Content, len(arguments.str_Content), 1) is
> arguments.str_Delimiter)
> {
> arrayAppend(ar_Tokens, "");
> }
> </cfscript>
>
> <cfreturn ar_Tokens />
> </cffunction>
>
> <cfif ThisTag.ExecutionMode is "Start">
> <!--- Read the file and inititialize position and caller
> variables. --->
> <cffile action="read" file="#attributes.str_Filename#"
> variable="str_Content" />
> <cfset int_CurrPos = 1 />
> <cfset caller.TextParse.str_Line = "" />
> <cfset caller.TextParse.ar_Tokens = arrayNew(1) />
> </cfif>
>
> <!--- Find the next delimiter. The regular expression
> "#attributes.str_LineDelimiter#|$"
> finds the next line delimiter OR the end of the string. --->
> <cfset int_NextPos = REFind("#attributes.str_LineDelimiter#|$",
> str_Content, int_CurrPos) />
>
> <cfif int_NextPos gt 0>
> <!--- Get and return the line and tokens. --->
> <cfset str_Line = mid(str_Content, int_CurrPos, int_NextPos -
> int_CurrPos) />
> <cfset caller.TextParse.str_Line = str_Line />
> <cfset caller.TextParse.ar_Tokens = TokenizeLine(str_Line,
> attributes.str_TokenDelimiter) />
>
> <!--- Reset the current position. --->
> <cfset int_CurrPos = int_NextPos + len(attributes.str_LineDelimiter)
> />
> </cfif>
>
> <cfif ThisTag.ExecutionMode is "End">
> <!--- Keep looping as long as we have content to process. --->
> <cfif int_NextPos gt 0>
> <cfexit method="Loop" />
> </cfif>
> </cfif>
>
>
> -------------------------------------------------------------
> To unsubscribe from this list, manage your profile @
>
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
>
> For more info, see http://www.acfug.org/mailinglists
> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> List hosted by FusionLink <http://www.fusionlink.com/>
> -------------------------------------------------------------
>
--
Douglas Knudsen
http://www.cubicleman.com
this is my signature, like it?
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink <http://www.fusionlink.com/>
-------------------------------------------------------------
--
<cf_payne />
Adobe Certified ColdFusion MX 7 Developer
Atlanta CFUG (ACFUG): http://www.acfug.org
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
