Hi,
I want to search Strings Like this.
Say Column containing ...
"A B"
"A B"
"A B"
"A B"
"A B"

How can I search "A B" with ignoring spaces. Result must return all the
records.

Thank you.
Dishan

Try this

Select column from table where left(column,1)='A' and
Right(column,1)='B'

Madhivanan

No. I just want to search "A B". but it must be return all values that
ignoring space at the middle.

On 5 Apr 2005 23:55:56 -0700, "Dishan Fernando" <dishan (AT) gmail (DOT) com> wrote:

Any way you might do it will be inefficient. Essentially, you have to write a
custom function that collapses strings of multiple spaces, and call it from
within the query, or open a recordset, loop through it, and call the compare
function for each row. Calling the function from within the query is simpler,
but looping through the recordset is more robust, and probably at least as
fast.

Here's one way you might write the function (not tested)...

Public Function CollapseSpaces(Value As String) As String
Dim strResult As String

strResult = Trim$(Value)
Do While Instr(strResult, " ") > 0
strResult = Replace(strResult, " ", " ")
Loop

CollapseSpaces = strResult
End Function

Oops - I thought I was reading the MS Access group. I'm not sure how you do
this in SQL Server, but it's probably a similar solution in TSQL to the VB/VBA
code I suggested.

On Wed, 06 Apr 2005 01:21:59 -0700, Steve Jorgensen <nospam (AT) nospam (DOT) nospam>
wrote:

select *
FROM Table1
WHERE REPLACE(COL,' ','') LIKE '%A B%' AND
Col NOT LIKE '%AB%'

On 6 Apr 2005 01:50:14 -0700, "Dishan Fernando" <dishan (AT) gmail (DOT) com> wrote:

I thought you needed the code to work when supplied with just 'A B' as the
criteria. I also thought you did not want to match, for instance 'XAB'.

The answer is in your question:

Use the 'Like' command.

Select X, y, z
From TableA
Where FieldA Like 'A%'

This tells SQL to return all rows starting with A and having
characters after. It will NOT return rows where the entire value is A.

You can of course do: Like 'AL%'
Like 'Almos%' etc.

The % sign is critical.

Note: this does not make use of your indexes. On large tables it can
be slow, it is definitely faster that doing a where right(FieldA, 1) =
'A'.

"Dishan Fernando" <dishan (AT) gmail (DOT) com> wrote

Dishan,
Try this also

select column from table where replace(column,' ','')='AB'