Thanks everyone.
Douglas Knudsen --> Will replace take care of tabs also??? or shall i need
to say chr(9) for example :
str_Token = Replace(trim(mid(arguments.str_Content, int_CurrPos, int_NextPos
- int_CurrPos)),' ','', 'All');
replaced with this code
<cfset str_Token = Replace(Replace(trim(mid(arguments.str_Content,
int_CurrPos, int_NextPos - int_CurrPos)),' ','', 'All',Chr(9),'All');>
Any suggestions?? should Chr(9) be in quotes???
Ajas Mohammed.
On 12/22/06, Teddy Payne <[EMAIL PROTECTED]> wrote:
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 FusionLink <http://www.fusionlink.com>
-------------------------------------------------------------
-------------------------------------------------------------
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
-------------------------------------------------------------
