Re: Searching a string with spaces and nospaces in a table.
From: Huiyong Lau (huiyong_lau_at_hotmail.com)
Date: 11/30/04
- Next message: Steve Kass: "Re: DST/UTC Fiasco"
- Previous message: Brad M.: "Re: DISTINCT Query returning Duplicates"
- In reply to: Meher Malakapalli: "Searching a string with spaces and nospaces in a table."
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 30 Nov 2004 15:04:04 +1100
Not sure in what situation you need to split the GC. My best guess is split
any capital letter that next to each other.
declare @var varchar(100)
set @var = 'GC Systems'
declare @cnt int, @prev_cap_char bit
select @cnt = 1, @prev_cap_char = 0
while @cnt <= len(@var)
begin
if ascii(substring(@var, @cnt, 1)) between 65 and 90
if @prev_cap_char = 1
select @var = stuff(@var, @cnt, 0, ' '), @cnt = @cnt + 1
else
set @prev_cap_char = 1
else
select @prev_cap_char = 0
set @cnt = @cnt + 1
end
set @var = replace(@var, ' ', '%')
select * from #org where orgname like @var
Regards,
Huiyong
"Meher Malakapalli" <mmalakapalli@cohesioninc.com> wrote in message
news:uAEPW4n1EHA.2712@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> How do I return string with spaces and no spaces using the same search
> criteria. For example I have the following table.
>
> Create table #org (RowID INT Identity(1,1),
> OrgName VARCHAR(40)
> )
>
> INSERT INTO #org Values('GC Systems')
> INSERT INTO #org Values('GC Systems')
> INSERT INTO #org Values('G C Systems')
> INSERT INTO #org Values('G C R Systems')
>
> Select * from #org
>
>
> I want to return all the 4 rows when the search criteria is LIKE GC
Systems
> or LIKE G C Systems. I dont want to hardcode the string name. I want to
pass
> the string as a variable to my select statement.
>
> Any help is appreiciated.
>
> Thanks
>
> DDL:
>
> Create table #org (RowID INT Identity(1,1),
> OrgName VARCHAR(40)
> )
>
> INSERT INTO #org Values('GC Systems')
> INSERT INTO #org Values('GC Systems')
> INSERT INTO #org Values('G C Systems')
> INSERT INTO #org Values('G C R Systems')
>
> Select * from #org
>
> Drop table #org
>
> M
>
>
- Next message: Steve Kass: "Re: DST/UTC Fiasco"
- Previous message: Brad M.: "Re: DISTINCT Query returning Duplicates"
- In reply to: Meher Malakapalli: "Searching a string with spaces and nospaces in a table."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
