February 21, 2006 at 11:03 am
I'm having an issue that hopefully someone on here can help with. I'm doing this in VB 6.0, but perhaps someone has some insight into how I can do it better or properly using SQL.
Basically I have a recordset that has some value taken from an Excell Spreadsheet.
An example of the recordset's value is
rsVDL = "This part is a ROHS Compliant widget."
Now I also have another recordset (rsROHSKEY)the values of which were populated from a SQL table. The values of this recordset are as follows:
"Lead Free", "Rohs Compliant", "Rohs".
What I want to do is for every value of rsVDL I want to replace any values that match rsROHSKEY with a nothing value ""
So let's say the value of rsVDL was "This part is a ROHS Compliant widget" after I run through the rsROHSKEY recordset and there is a match between rsVDL and rsROHSKEY on the string fragment "Rohs Compliant" I want the ending value of rsVDL to be
"This is a widget" (Notice "Rohs Compliant" is gone)
I've tried to use the Replace method and that hasn't worked.
I've tried to pass the value of rsVDL to a string and then to use the replace method.
Any suggestions?
Here is loop as I currently have it:
rsROHSKEY.MoveFirst
Do While rsROHSKEY.EOF = False
strNEWDESC = Replace(rsVDL.Fields("DDDSC").Value, rsROHSKEY.Fields("ROHSKEY").Value, "")
If strNEWDESC <> rsVDL.Fields("DDDSC").Value Then Exit Do
rsROHSKEY.MoveNext
Loop
February 21, 2006 at 12:38 pm
Upper/lower case doesn't matter?
February 21, 2006 at 12:39 pm
declare @Word table (Word varchar(255))
declare @String varchar(255)
select @String = 'This part is a ROHS Compliant widget.'
insert @Word values ('Lead Free')
insert @Word values ('Rohs Compliant')
insert @Word values ('Rohs')
select @String = replace(@String, w.Word, '')
from @Word w
select @String
February 21, 2006 at 12:42 pm
Jo: No...upper lowercase doesn't matter.
JeffB: Thanks for the suggestion. I will give it a go and let you know what happens.
J
February 21, 2006 at 12:51 pm
For case sensitivity try:
declare @Word table (Word varchar(255))
declare @String varchar(255)
select @String = 'This part is a ROHS Compliant widget.'
insert @Word values ('Lead Free')
insert @Word values ('Rohs Compliant')
insert @Word values ('Rohs')
select @String = replace(@String, w.Word, '')
from @Word w
where @String like '%' + w.Word + '%' COLLATE SQL_Latin1_General_CP1_CS_AS
select @String
February 21, 2006 at 2:26 pm
Thanks everyone for your help.
This is what I ended up doing...
rsROHSKEY.MoveFirst
Do While rsROHSKEY.EOF = False
intROHS = InStr(UCase(rsVDL.Fields("DDDSC").Value), UCase(rsROHSKEY.Fields("ROHSKEY").Value))
If intROHS > 0 Then
intROHSLENGTH = Len(rsROHSKEY.Fields("ROHSKEY").Value)
intROHSPOS = intROHS - 1
strL = Left(rsVDL.Fields("DDDSC").Value, intROHSPOS)
intLenLeft = Len(strL)
strR = Right(rsVDL.Fields("DDDSC").Value, (Len(rsVDL.Fields("DDDSC").Value) - (intLenLeft + intROHSLENGTH)))
strNEWDESC = RTrim(strL) & " " & LTrim(strR)
Else
strNEWDESC = rsVDL.Fields("DDDSC").Value
End If
If strNEWDESC <> rsVDL.Fields("DDDSC").Value Then Exit Do
rsROHSKEY.MoveNext
Loop
However, I will use your suggestions very soon as I will have to do the same project on the SQL server using SQL Expression.
Thanks again,
Justyna
February 28, 2006 at 1:22 pm
In T-SQL you need to use nested REPLACE funtions:
select
replace(replace(replace(
vdl,'Lead Free ',''),
'Rohs Compliant ',''),
'Rohs ','')
from ...
In Visual Basic I would use a Regular Expression object. This code would work in VB.NET, in VB6 you would reference the Microsoft Scripting Library to get regular expression objects. I believe the pattern string would be the same, and the Replace method is very similar.
Imports System.Text.RegularExpressions
strNEWDESC = Regex.Replace(strDDDSC, "\b(Lead Free|Rohs Compliant|Rohs)\s+", "", RegexOptions.IgnoreCase Or RegexOptions.Singleline)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply