SQL Like.

[Dishan Fernando]

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

[Madhivanan]

Re: SQL Like.


Try this

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

Madhivanan

[Dishan Fernando]

Re: SQL Like.

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

[Steve Jorgensen]

Re: SQL Like.

On 5 Apr 2005 23:55:56 -0700, "Dishan Fernando" <dishan@gmail.c om> wrote:
[color=blue]
>No. I just want to search "A B". but it must be return all values that
>ignoring space at the middle.[/color]

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(strResu lt, " ", " ")
Loop

CollapseSpaces = strResult
End Function

[Steve Jorgensen]

Re: SQL Like.

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@nospam. nospam>
wrote:
[color=blue]
>On 5 Apr 2005 23:55:56 -0700, "Dishan Fernando" <dishan@gmail.c om> wrote:
>[color=green]
>>No. I just want to search "A B". but it must be return all values that
>>ignoring space at the middle.[/color]
>
>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(strResu lt, " ", " ")
> Loop
>
> CollapseSpaces = strResult
>End Function[/color]

[Dishan Fernando]

Re: SQL Like.


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

[Steve Jorgensen]

Re: SQL Like.

On 6 Apr 2005 01:50:14 -0700, "Dishan Fernando" <dishan@gmail.c om> wrote:
[color=blue]
>
>select *
>FROM Table1
>WHERE REPLACE(COL,' ','') LIKE '%A B%' AND
>Col NOT LIKE '%AB%'[/color]

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'.

[Sandy]

Re: SQL Like.

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@gmail.c om> wrote in message news:<111276026 8.160997.96920@ l41g2000cwc.goo glegroups.com>. ..[color=blue]
> 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[/color]

[Madhivanan]

Re: SQL Like.


Dishan,
Try this also

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