Query on free text field

Question

EDIT:
Actually, I run MSSQL query, let say the result is:

ID  pagename
1   1
2   01
3   01, 15

Then, I run another command URL and got the result as xml data, let say the result (in simple form) is:

4   01, 01 Aaa, 15
5   02
6   03
7   100
8   101
9   115

Using coldfusion, I can merge both data into one "temporary table". So, actually, I am using a QoQ and not a database query
END EDIT

I have a table like this

ID  pagename
1   1
2   01
3   01, 15
4   01, 01 Aaa, 15
5   02
6   03
7   100
8   101
9   115

Is it possible if I want to show pagename = 1 the result is

ID  pagename
1   1
2   01
3   01, 15
4   01, 01 Aaa, 15

Answer 1

I think that you will have better luck with programming code than query of queries. My approach would resemble this:

<cfset NewQuery = QueryNew("id,pagename","integer,varchar")>
<cfloop query = "ExistingQuery">
  <cfif ListFirst(pagename) EQ 1>
    code to add row and set cell values for NewQuery
  </cfif>
</cfloop>

Note to those reading on the sql page, this is an earlier comment: "@MahmoudGamal sorry, I'm using Coldfusion function QueryNew to create temporary table"

In other words, it's not a database query.

Answer 2

Solution for MSSQL (robust on purpose, the parselist function can help you normalize db to something more sane)

Helping functions:

CREATE FUNCTION [dbo].[udf_GetNumeric]
    (@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
    BEGIN
        WHILE @intAlpha > 0
        BEGIN
            SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
            SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
        END
    END
    RETURN ISNULL(@strAlphaNumeric,0)
END
GO

CREATE FUNCTION [dbo].[ParseList_IntAny]
(   
  @List nvarchar(1000)
)
RETURNS @Result TABLE (
  IntValue int not null
)
as
begin
  declare @Value nvarchar(20), @Position int
  select @List = LTRIM(RTRIM(@List))+ ','
  select @Position = CHARINDEX(',', @List, 1)

  if REPLACE(@List, ',', '') <> ''
  begin
    while @Position > 0
      begin
        select @Value = LTRIM(RTRIM(LEFT(@List, @Position - 1)))
        if @Value <> ''
        begin
          declare @IntValue int
          select @IntValue = dbo.udf_GetNumeric(@Value)
          insert into @Result(IntValue) values (@IntValue)
        end
        select @List = RIGHT(@List, LEN(@List) - @Position)
        select @Position = CHARINDEX(',', @List, 1)
      end
  end
  return
end
GO


declare @tmp table(ID int,  pagename nvarchar(400))
insert into @tmp
select 1,'1'
union select 2,'01'
union select 3,'01, 15'
union select 4,'01, 01 Aaa, 15'
union select 5,'02'
union select 6,'03'
union select 7,'100'
union select 8,'101'
union select 9,'115'

select * from @tmp
where exists(select top 1 1 from dbo.ParseList_IntAny(pagename) where IntValue = 1)