CODEWITHSUNDEEP
sqlsql-server-2008postgresqlpostgresql-9.2
skywalker2909
skywalker2909

Reputation: 1686

Query for displaying related usernames that match a given username

What would the query be if lets say the person searches for a username --> johnny123 and if there is no such username, then i want it to return a list usernames that match the pattern of the username entered like johnny12345 , johnnyboy etc.

Upvotes: 0

Views: 2818

Answers (3)

Ali Shah Ahmed
Ali Shah Ahmed

Reputation: 3333

try this out:

select * from table_name
where username like '%johnny%';

This will return all the rows that have johnny in their username. For example, if your table contains these three usernames:

johnny123
123johnny
123johnny456

All the rows will be returned as they contain johnny in their username.

Upvotes: 1

fbynite
fbynite

Reputation: 2661

This will return a single match for johnny123. Otherwise, if no johnny123 is found, it will return everything that starts with 'johnny'. Not elegant, but it does what you described.

Using the function below, you would go select * from username.test('username');

create function username.test(text)
  returns setof text as $$
  declare
    usr text;
  begin
    usr := (select username from tablename where v like $1);
  if (usr is not null)
    then
       return next usr;     
  else
    return query (select username from tablename where v like '' || $1 || '%'); 
  end if;
  return;
  end;
  $$ language 'plpgsql';

Upvotes: 1

Paz
Paz

Reputation: 694

This will work:

SELECT * FROM tablename WHERE username like 'johnny%';

This will search all the username starting with "johnny".

Upvotes: 2

Related Questions