Warning: Creating default object from empty value in /homepages/42/d301471907/htdocs/wp-includes/functions.php on line 334
C l e a r G l a s s ~ Blog
CG Rotating Images

Welcome to C l e a r G l a s s

SQL String Parsing Function


Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /homepages/42/d301471907/htdocs/wp-includes/functions-formatting.php on line 76

I’ve found this awesome string parsing function for SQL written by Nick Barclay from Australia. The function returns the character position of the character after the Nth instance of the search string.

SQL Code:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO
 
CREATE FUNCTION InString(
 @string varchar(200),
 @searchfor varchar(50),
 @position int
) returns int
 
AS
BEGIN
DECLARE @lenstring int, @poscount int, @stringpos int
SET @lenstring = datalength(@searchfor)
SET @poscount = 1
SET @stringpos = 1
WHILE @poscount < = @position and @stringpos <= len(@string)
 – string segment found
 BEGIN
  IF substring(@string, @stringpos, @lenstring)=@searchfor
  BEGIN
   – is the one we are looking for?
   IF @poscount = @position
    BEGIN
     SET @stringpos = @stringpos + @lenstring
     return @stringpos
    END
   ELSE – look for the next instance
    BEGIN
     SET @poscount = @poscount + 1
    END
  END
  SET @stringpos = @stringpos + 1
 END
 return null
END
GO
 
GRANT EXEC on dbo.InString TO PUBLIC
GO

Example 1:

Returns 18
select dbo.InString('123456 123456 123456 123456', '23', 3)

Example 2:

Returns 25
select dbo.InString('test1/test2/test3/test4/test5/', '/', 4)

Example 3:

Returns ‘test4’
declare @teststring varchar(50)
set @teststring = ‘test1/test2/test3/test4/test5/’
select substring(@teststring, dbo.Instring(@teststring,’/',3),5)

Example 4:

Returns ‘textxyz’
(variable length delimited fields
declare @teststring2 varchar(50)
set @teststring2 = ‘test123/test/testtestestest/testxyz/test/’
select substring( @teststring2,
dbo.Instring(@teststring2, ‘/’, 3),
(dbo.Instring(@teststring2, ‘/’, 4) -1)
- dbo.Instring(@teststring2, ‘/’, 3)
)

— admin @ 1:55 pm 8/6/2005

Conditional Statements with MS Access


Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /homepages/42/d301471907/htdocs/wp-includes/functions-formatting.php on line 76

How do you do conditional if (), else if(), else statements in MS Access for the control source using the Expression Builder?

Here is the format:

=IIf(condition, true, false)

Example 1:

=IIf([isOfficer]="0","Non-Officers","Officers")

Pseudocode:

If (isOfficer = "0") Then
 Print “Non-Officers”
Else
 Print “Officers”
End If

Example 2:

What if you want to add another conditional statement? The only solution is to nest another IIf() function in the False part of the Condition.
=IIf([isOfficer]="0","Non-Officers",IIf([isOfficer]="-1","All Officers","Both"))

Pseudocode:

If (isOfficer = "0") Then
 Print “Non-Officers”
Else If (isOfficer = “-1″) Then
 Print “All Officers”
Else
 Print “Both”
End If

— admin @ 9:15 am 8/5/2005

ClearGlass LLC © 2009-2011. All rights reserved.