SQLServer无法判断空格数量

最新推荐文章于 2024-06-24 17:53:11 发布

原创最新推荐文章于 2024-06-24 17:53:11 发布 · 2.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #insert #table #null #脚本 #测试

SQLServer 专栏收录该内容

11 篇文章

订阅专栏

本文通过SQLServer脚本展示了如何在数据库中处理字符串中连续的空数据量，包括创建测试表、插入数据及查询操作。

在SQLServer中字符串中连续的空格数据量是无法判断的，以下是测试脚本：

DROP TABLE [dbo].[TestTable]
CREATE TABLE [dbo].[TestTable](
[Id] [int] NOT NULL,
[Name] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

insert into TestTable(id, [Name]) values (1, '')
insert into TestTable(id, [Name]) values (2, ' ')
insert into TestTable(id, [Name]) values (3, ' ')

select id, 'XX' + [Name] + 'YY' from TestTable where [Name] = ''
select id, 'XX' + [Name] + 'YY' from TestTable where [Name] = ' '
select id, 'XX' + [Name] + 'YY' from TestTable where [Name] = ' '

exec sp_executesql N'SELECT * FROM [dbo].[TestTable] WHERE ([Name] <> @Name1 )',N'@Name1 char(1)',@Name1=' '
exec sp_executesql N'SELECT * FROM [dbo].[TestTable] WHERE ([Name] <> @Name1 )',N'@Name1 char(8000)',@Name1=' '
exec sp_executesql N'SELECT * FROM [dbo].[TestTable] WHERE ([Name] <> @Name1 )',N'@Name1 varchar(1)',@Name1=' '

exec sp_executesql N'SELECT * FROM [dbo].[TestTable] WHERE ([Name] = @Name1 )',N'@Name1 char(8000)',@Name1=' '
exec sp_executesql N'SELECT * FROM [dbo].[TestTable] WHERE ([Name] = @Name1 )',N'@Name1 char(1)',@Name1=' '
exec sp_executesql N'SELECT * FROM [dbo].[TestTable] WHERE ([Name] = @Name1 )',N'@Name1 varchar(5)',@Name1=' '