How to implement sql search on several fields, taking into account the fact that the query can contain two fields at once?

Question

SQL

How to implement sql search on multiple fields? The example does not work correctly

select id,name,surname from users WHERE name LIKE '%Роман%' OR surname LIKE '%Роман%'

Now there is a search and we are looking for the value "Roman" in two fields. In the same way, the query "Rakzin" displays the last name ....
but if you enter "Rakzin Roman", then since there is no field, naturally nothing is displayed. I need to make a request that this option worked. How to implement this?

Answer 1

SELECT ID, Name + ' ' + Surname AS LikeName FROM Users WHERE LikeName = 'Ракзин Роман'

If you want all 'Roman' and all 'Rakzin'

SELECT ID, Name + ' ' + Surname AS LikeName FROM Users 
WHERE (LikeName LIKE '%Ракзин%') OR  (LikeName LIKE '%Роман%')

Real search of films, their attributes and persons.

Search view

CREATE VIEW SearchLike
AS
  SELECT Film.ID, 
         ISNULL(CAST(Film.Year AS VARCHAR) + ' ', '') + ISNULL(Head.Name + ' ' + Film.HeadingNumber + ' ', '') 
           + ISNULL(Film.Article + ' ', '') + ISNULL(Film.Name + ' ', '') + ISNULL(Film.NameTranslat + ' ', '') 
           + ISNULL(Film.Addition + ' ', '') + ISNULL(Film.Country + ' ', '') 
           + ISNULL(FilmAttributeGroup.Name + ' ', '') + ISNULL(FilmAttributeValue.Value + ' ', '') AS Search,
         Film.[Key]
    FROM FilmAttributeValue 
      INNER JOIN FilmAttribute ON FilmAttributeValue.ID = FilmAttribute.Attribute 
      INNER JOIN FilmAttributeGroup ON FilmAttributeValue.[Group] = FilmAttributeGroup.ID 
      RIGHT OUTER JOIN Film ON FilmAttribute.Film = Film.ID 
      LEFT OUTER JOIN Film AS Head ON Film.Heading = Head.ID
  --
  UNION
  --
  SELECT Person, 
         [Name], 
         'P' + CAST(Person AS VARCHAR)
    FROM PersonNameFormat
    WHERE ([Format] > 10) OR ([Format] = 0)
--
GO

Name representation

CREATE VIEW SearchName
AS
  -- Фильмы:
  SELECT Film.ID, 
         FilmNamePower.[Year]     AS [Year], 
         FilmNamePower.NameCommon AS [Name], 
         Film.WatchDisplay,
         Film.Icon,
         Film.AttributeIcon,
         '0' + Film.[Range] AS [Range],
         Film.[Key],
         Film.ID AS UnitID
    FROM Film INNER JOIN FilmNamePower ON Film.ID = FilmNamePower.ID
  --
  UNION
  -- Персоны:
  SELECT ID, 
         ISNULL(YEAR(BirthDay), 1900), 
         NameFull, 
         '', 
         NoteIcon, 
         NULL, 
         '1' + CONVERT(VARCHAR, ISNULL(BirthDay, '19000101'), 112) + [Range], 
         [Key],
         -1
    FROM Person    
--
GO

Grid, search up to three parameters

CREATE PROCEDURE SearchGrid @P1 VARCHAR(100) = NULL, @P2 VARCHAR(100) = NULL, @P3 VARCHAR(100) = NULL
AS
  SELECT DISTINCT
         SearchName.ID, 
         SearchName.[Year], 
         SearchName.[Name], 
         SearchName.WatchDisplay, 
         SearchName.Icon,
         SearchName.AttributeIcon,
         SearchName.[Range],
         LEFT(SearchName.[Key], 1) AS Kind,
         SearchName.UnitID
    FROM SearchName INNER JOIN SearchLike ON SearchName.[Key] = SearchLike.[Key]
    WHERE ((NOT @P1 IS NULL) AND (SearchLike.Search LIKE '%' + @P1 + '%'))
       OR ((NOT @P2 IS NULL) AND (SearchLike.Search LIKE '%' + @P2 + '%')) 
       OR ((NOT @P3 IS NULL) AND (SearchLike.Search LIKE '%' + @P3 + '%')) 
    ORDER BY SearchName.[Range]
--
GO