Syntax - How to use variable in LIKE clause

TreyS 166

Trying to use a variable in the LIKE clause of a SELECT statement.

Hope this is simple, but available guidance hasn't worked.

As seen below, query works when a "fixed" value is used with wildcard characters, but I haven't managed to get it with the variable...

--The Workdays column, in c_Persons table, is NCHAR(7)

--This works
--SELECT * FROM c_Persons WHERE Workdays LIKE '%3%'

DECLARE @DOW NCHAR(7)

SET @DOW = '%3%'
--This returns nothing.
--SELECT * FROM c_Persons WHERE Workdays LIKE @DOW

SET @DOW = '3'
--This returns nothing.
--SELECT * FROM c_Persons WHERE Workdays LIKE '%@DOW%'

--This returns nothing.
SELECT * FROM c_Persons WHERE Workdays LIKE '%' + @DOW + '%'

TreyS 166

Yes, as stated,

SELECT * FROM c_Persons WHERE Workdays LIKE '%3%'

Returns data.

Here is table content...

TenantID PersonID FirstName MiddleName LastName UserName IsCompanyAdmin IsCustomer IsGroomer IsBather Internal AddressID DateCreated DateLastUpdate Status DateStatusUpdate DateLastLogon HourlyRate GroomingPct BatherPct Currency PrimaryDevice Workdays StartHour StopHour TimeZone GroomerUnits Language Email PasswordHashed PasswordHistory DateLastPasswordChange

B3C069AE-EA86-4832-81E9-9DA16AE66CA1 172e06aa-3851-4977-8cfa-c7b2496e92c4 Barbara Lynn Shaffer Barb@Shaffer-Family.com 0 0 0 0 1 484884d4-742f-4ff3-a802-2c5dcd08c605 2024-02-27 10:39:31.000 2024-05-04 11:21:07.000 B. Status 2024-03-21 11:49:04.000 NULL 15.25 NULL NULL USD NULL 0123456 0 24 Central Standard Time 0 EN NULL 3E0777FE058A7E16F46420A02F3F7C9A 6C36424475C382479BAA26777F230E36,3620A632D3CE95B0CCCF650A4E24B860,3E0777FE058A7E16F46420A02F3F7C9A 2024-05-04 11:21:07.000

B3C069AE-EA86-4832-81E9-9DA16AE66CA1 1965c07c-706b-46a9-877e-990a92614006 Trey NULL Shaffer Trey@Shaffer-Family.com 1 0 0 0 1 484884d4-742f-4ff3-a802-2c5dcd08c605 2024-01-01 10:39:31.000 1900-01-01 00:00:00.000 T. Status 2024-02-28 15:00:55.000 NULL NULL NULL NULL USD NULL 0123456 0 24 Central Standard Time 0 EN NULL D0DF3E61FD8CB7BCFC93B99B1FB39AB9 NULL

B3C069AE-EA86-4832-81E9-9DA16AE66CA1 b9ba676b-be45-486b-a08b-e079081569dd First NULL Groomer Grooming01@GMail.com 0 1 1 0 1 328be3f3-74a1-40d1-a901-effaf46f194b 2024-03-23 10:30:37.000 1900-01-01 00:00:00.000 Active NULL NULL NULL NULL NULL USD NULL 23456 0 24 Central Standard Time 10 EN NULL F5A0CBDD38AB00EC7FBABCA9563CA125 NULL

B3C069AE-EA86-4832-81E9-9DA16AE66CA1 d50e597a-46c7-48d7-847c-b3c0f38fa741 George NULL Jetson George.Jetson@SpacelySprockets.com 0 1 0 0 1 c81f4878-c1d6-4c7f-9d7a-ef8c3a6cc5d2 2024-05-10 15:09:52.000 2024-05-10 15:09:52.000 Active 2024-05-10 15:09:52.000 NULL NULL NULL NULL USD NULL 0123456 0 24 Central Standard Time 0 EN George.Jetson@SpacelySprockets.com NULL NULL NULL

Here is the table structure...

USE [MyCloudGroomer]
GO

/****** Object:  Table [dbo].[c_Persons]    Script Date: 6/20/2024 2:00:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[c_Persons](
	[TenantID] [nvarchar](50) NOT NULL,
	[PersonID] [nvarchar](50) NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[IsCompanyAdmin] [bit] NOT NULL,
	[IsCustomer] [bit] NOT NULL,
	[IsGroomer] [bit] NULL,
	[IsBather] [bit] NULL,
	[Internal] [bit] NOT NULL,
	[AddressID] [nvarchar](50) NOT NULL,
	[DateCreated] [datetime] NULL,
	[DateLastUpdate] [datetime] NULL,
	[Status] [nchar](10) NULL,
	[DateStatusUpdate] [datetime] NULL,
	[DateLastLogon] [datetime] NULL,
	[HourlyRate] [money] NULL,
	[GroomingPct] [float] NULL,
	[BatherPct] [float] NULL,
	[Currency] [nvarchar](50) NULL,
	[PrimaryDevice] [nvarchar](50) NULL,
	[Workdays] [nchar](7) NOT NULL,
	[StartHour] [float] NOT NULL,
	[StopHour] [float] NOT NULL,
	[TimeZone] [nvarchar](50) NOT NULL,
	[GroomerUnits] [int] NULL,
	[Language] [nchar](2) NOT NULL,
	[Email] [nvarchar](255) NULL,
	[PasswordHashed] [nvarchar](329) NULL,
	[PasswordHistory] [nvarchar](255) NULL,
	[DateLastPasswordChange] [datetime] NULL,
 CONSTRAINT [PK_c_Persons] PRIMARY KEY CLUSTERED 
(
	[TenantID] ASC,
	[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_IsCompanyAdmin]  DEFAULT ((0)) FOR [IsCompanyAdmin]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_Customer]  DEFAULT ((0)) FOR [IsCustomer]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_IsGroomer]  DEFAULT ((0)) FOR [IsGroomer]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_IsBather]  DEFAULT ((0)) FOR [IsBather]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_Internal]  DEFAULT ((1)) FOR [Internal]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_Currency]  DEFAULT (N'USD') FOR [Currency]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_Workdays]  DEFAULT ((1234567)) FOR [Workdays]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_StartHour]  DEFAULT ((0)) FOR [StartHour]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_StopHour]  DEFAULT ((24)) FOR [StopHour]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_TimeZone]  DEFAULT (N'Central Standard Time') FOR [TimeZone]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_GroomerUnits]  DEFAULT ((0)) FOR [GroomerUnits]
GO

ALTER TABLE [dbo].[c_Persons] ADD  CONSTRAINT [DF_c_Persons_Language_1]  DEFAULT (N'EN') FOR [Language]
GO

TreyS 166 Reputation points

2024-06-20T19:12:13.82+00:00

Expected result is all five rows, which contain '3' in Workdays column.

Accepted answer

Erland Sommarskog 105.8K Reputation points MVP

2024-06-20T21:57:17.5333333+00:00
You have declare @DOW as nchar(7). That is, it's fixed length of seven characters. So if you do:

SET @DOW = '%3%' --This returns nothing. --SELECT * FROM c_Persons WHERE Workdays LIKE @DOW

You are search for strings that include the digit three followed by six spaces.

Change the datatype to nvarchar(7), and you should be fine.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

1 additional answer

Olaf Helper 42,761 Reputation points

2024-06-20T16:30:35.9033333+00:00

--This returns nothing. --SELECT * FROM c_Persons WHERE Workdays LIKE '%@DOW%'

Of course not, I don't know a day name containing "@DOW".

Please post table design as DDL, some sample data as DML and the expected result.
Please sign in to rate this answer.
TreyS 166 Reputation points

2024-06-20T16:49:30.6766667+00:00

Thank you.

The Workdays column/field in table, c_Persons is a string, NCHAR(7), consisting of any/all numerals 1,2,3,4,5,6,7.

As mentioned, using those value(s), with pre/post %, generates the expected result. My issue is assigning target value to a variable and then using the variable.

Olaf Helper 42,761 Reputation points

2024-06-20T17:09:42.43+00:00

Again, Please post table design as DDL, some sample data as DML and the expected result.

Does the fix code

SELECT * FROM c_Persons WHERE Workdays LIKE '%3%'

return a result?
Sign in to comment

Share via

Syntax - How to use variable in LIKE clause

1 additional answer