how to select colums with value %%

[patnim17]

Hi,
I have a colum in my table that has values that starts with either %% or %

I need to select all the records whose column value start with just %..

how can I do that..

nims17

 

[Zathras]

It's only a problem when using LIKE. If your db supports it you can use something like this (SQL Server syntax):
[tt]
Select * from MYTABLE where Left(MYCOLUMN,1) = '%'
[/tt]
If that doesn't do it for you, post again and this time reveal what database you are using.

 

[patnim17]

that would pull records that have column values that start with '%%' also...I wanted only those records that start with '%' and not '%%'

 

[Zathras]

How about exercising a little creativity here?
[tt]
Select * from MYTABLE
where Left(MYCOLUMN,1) = '%'
and Left(MYCOLUMN,2) = '%%'
[/tt]
or
[tt]
Select * from MYTABLE
where Left(MYCOLUMN,1) = '%'
and Mid(MYCOLUMN,1,1) = '%'
[/tt]

 

[Zathras]

Sorry, I meant

Select * from MYTABLE
where Left(MYCOLUMN,1) = '%'
and Left(MYCOLUMN,2) <> '%%'

or

Select * from MYTABLE
where Left(MYCOLUMN,1) = '%'
and Mid(MYCOLUMN,1,1) = '%'

 

[JarlH]

select * from sometable
where SUBSTRING(c1 from 1 for 1) = '%'
and SUBSTRING(c1 from 2 for 1) <> '%';

Pure ANSI SQL.

 

[juliane26]

AFAIK most DBMS understand SUBSTR(c1,1,1) and SUBSTR(c1,2,1)

SUBSTR(string, start position, length)

which database does the from .. for .. syntax ?


Juliane

 

[JarlH]

The "FROM FOR" syntax is as defined by the ISO/ANSI SQL standard, and is supported by Mimer SQL, MySQL, SQLite, Postgres etc.

SUBSTRING(name ,1 ,2) is supported by SQL Server and Sybase.

SUBSTR(name,1,2) by DB2 and Oracle.