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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy