SQL Query

[msofficebeginner]

Qhat is the SQL commad/function to find out how many spaces are there in a string.?

EX: Name: <- FieldName
Kat Rome <- row data
Cleo Rome <- row data
Patra Rome <- row data
Me at Rome <- row data


I want to find out in the data, how many spaces are there.
ex: Kat Rome : 1 space
Me at Rome: 2 Spaces


Please provide a SQL Command or Statement.

Thanks in advance.

 

[M8KWR]

Hi there, so you are trying to do this within access...

If you create a module and insert the following

Function RemoveAlphas(ByVal AlphaNum As Variant)

   Dim Clean As String
   Dim SpaceNum As Integer
   Dim Pos, A_Char$

   Pos = 1
   If IsNull(AlphaNum) Then Exit Function

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ = " " Then
         Clean$ = Clean$ + A_Char$
         SpaceNum = SpaceNum + 1
      End If
   Next Pos

   RemoveAlphas = SpaceNum

End Function

Create a query and type the following in the field

Expr1: removealphas([FIELD_NAME])


I hope this helps.... I do not know if there is an inbuilt function within access to do this without creating a module. I use this module for many things, and is very adaptable, to remove numbers or characters from a string etc.

Let me know how you get on.

 

[Remou]

Or a similar idea:

SQL:
[tt]SELECT CountSpace([FieldName]) AS Spaces
FROM tblTable;[/tt]

Function:

Function CountSpace(ByVal TheStr As String) As Integer
Dim i As Integer

i = 0

Do While InStr(TheStr, " ") > 0
TheStr = Replace(TheStr, " ", "", , 1)
i = i + 1
Loop
CountSpace = i
End Function

 

[msofficebeginner]

Thanks for the reply,

hello remou, I need that SQL statement, but it does not work.

I didnot uderstand what the code is give. or how shall I implement that.

 

[TheAceMan1]

How are ya msofficebeginner . . .

Perhaps this:

[blue]Public Function SpcCnt(ByVal Fld As String)
   SpcCnt = Len(Fld) - Len(Replace(Fld, " ", ""))
End Function[/blue]

See Ya! . . . . . .

 

[Remou]

What you need to do is to paste the code given by AceMan or me into a module. Then, in the SQL window of the query designer, paste the SQL lines in my post. If you are using AceMan's code, you will need to change the SQL to take account of that. For example:
SQL:
SELECT SpcCnt ([FieldName]) AS Spaces
FROM tblTable;