May 12, 2009 at 2:03 pm
Left([Add1],(InStr(1,[Add1],".")-1)) & Right([Add1],Len([Add1])-(InStr(1,[Add1],".")))
May 12, 2009 at 2:16 pm
What does "InStr" do?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2009 at 3:05 pm
InStr() is an alternate version of Mid(), sort of like Substring().
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 3:07 pm
Nope, sorry, I got mixed up (long time since I used it), InStr(..) is the VB/Access version of Charindex() (there used to be another version called Loc() also).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 3:08 pm
A visual studio (VB and I presume C#)
Returns an integer specifying the start position of the first occurrence of one string within another.
Public Shared Function InStr(_
ByVal String1 As String, _
ByVal String2 As String, _
Optional ByVal Compare As CompareMethod _
) As Integer
' -or-
Public Shared Function InStr(_
ByVal Start As Integer, _
ByVal String1 As String, _
ByVal String2 As String, _
Optional ByVal Compare As Microsoft.VisualBasic.CompareMethod _
) As Integer
May 12, 2009 at 7:45 pm
How can I write it at store procedure. Thank you
May 12, 2009 at 8:23 pm
Krasavita (5/12/2009)
How can I write it at store procedure. Thank you
First, is Add1 a column or a variable?
Secondly, we can not convert this to a Stored Procedure (which is like a Query Definition only more so) because it is just an expression. what you have show us doesn't do anything, it just calculates a value.
Converting the expression to T-SQL would look like this:
Left([Add1], Charindex('.',[Add1],1)-1 ) + Right([Add1], Len([Add1])-Charindex('.',[Add1],1) )
(edit: corrected stray InStr() )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 6:26 am
column
May 13, 2009 at 6:31 am
This is what I am trying to do: UPDATE TblNoRent SET TblNoRent.Add1 = Left([Add1],(InStr(1,[Add1],".")-1)) & Right([Add1],Len([Add1])-(InStr(1,[Add1],".")))
WHERE (((TblNoRent.Add1) Like ("*.*") And (TblNoRent.Add1) Not Like ("*#.#*")));
May 13, 2009 at 7:30 am
Like this then:
Create proc spMyAccessProc
AS
UPDATE TblNoRent
SET TblNoRent.Add1 = Left(Add1, Charindex('.',Add1,1)-1 )
+ Right(Add1, Len(Add1)-Charindex('.',Add1,1) )
WHERE ((TblNoRent.Add1 Like ("%.%")
And (TblNoRent.Add1) Not Like ("%[0-9].[0-9]%")));
-- not sure if "#" means "[0-9]" or not?
(edit: corrected stray InStr() )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply