sql 在字符串中查找子字符串

CHARINDEX

返回字符串中指定表达式的起始位置。

语法

CHARINDEX ( expression1 , expression2 [ , start_location ] )

参数

expression1

一个表达式，其中包含要寻找的字符的次序。expression1 是一个短字符数据类型分类的表达式。

expression2

一个表达式，通常是一个用于搜索指定序列的列。expression2 属于字符串数据类型分类。

start_location

在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定start_location，而是一个负数或零，则将从 expression2 的起始位置开始搜索。

返回类型

int

注释

如果 expression1 或 expression2 之一属于 Unicode 数据类型（nvarchar 或 nchar）而另一个不属于，则将另一个转换为 Unicode 数据类型。

如果 expression1 或 expression2 之一为 NULL 值，则当数据库兼容级别为 70 或更大时，CHARINDEX 返回 NULL 值。当数据库兼容级别为 65 或更小时，CHARINDEX 仅在 expression1 和 expression2 都为 NULL 时返回 NULL 值。

如果在 expression2 内没有找到 expression1，则 CHARINDEX 返回 0。

示例

第一个代码示例返回序列"wonderful"在 titles 表的 notes 列中开始的位置。第二个示例使用可选的 start_location 参数从 notes 列的第五个字符开始寻找"wonderful"。第三个示例显示了当 expression2 内找不到expression1 时的结果集。

USE pubs

SELECT CHARINDEX('wonderful', notes)

FROM titles

WHERE title_id = 'TC3218'

-- Use the optional start_location parameter to start searching -- for wonderful starting with the fifth character in the notes

-- column.

USE pubs

SELECT CHARINDEX('wonderful', notes, 5)

FROM titles

WHERE title_id = 'TC3218'

下面是第一个查询和第二个查询的结果集：

-----------

(1 row(s) affected)

USE pubs

SELECT CHARINDEX('wondrous', notes)

FROM titles

WHERE title_id='TC3218'

下面是结果集。

-----------

(1 row(s) affected)

----------------------------------------------------------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[tb001]') and ObjectProperty(id, N'IsUserTable') = 1) drop table [tb001]

if exists (select * from dbo.sysobjects where id = object_id(N'[tb002]') and ObjectProperty(id, N'IsUserTable') = 1) drop table [tb002]

---------------------------------------------------------------------------------------------------------------------------------------- select [xodid]=0,----integer----

[xTableName]=N'TableName',

[xColumn_id]=N'NO.',

[xColumnName]=N'ColumnName',

[xCaption]=N'Caption',

[xType]=N'DataType',

[xPrimaryKey]=N'PrimaryKey',

[xDefault]=N'Default',

[xNullAble]=N'NullAble',

[xColumnDesc]=N'Description',

[xAccess1]=N'Access',

[xAccess2]=N'Access',

[xAccess3]=N'Access'

into tb001

/*----*/

select [xodid]=C.column_id,----integer----

[xTableName]=Case When C.column_id=1 then https://www.360docs.net/doc/e912191272.html, else N'' end,

[xColumn_id]=CAST(C.column_id AS varchar(8)),

[xColumnName]=https://www.360docs.net/doc/e912191272.html,,

[xCaption]=N'',

--[xType]=(rtrim(ltrim(https://www.360docs.net/doc/e912191272.html,))+'('+CAST(C.max_length AS

varchar(8))+','+CAST(C.precision AS varchar(8))+','+CAST(C.scale AS varchar(8))+')'),

[xType]=(

case when rtrim(ltrim(https://www.360docs.net/doc/e912191272.html,))in ('char','varchar','nchar','nvarchar') then (rtrim(ltrim(https://www.360docs.net/doc/e912191272.html,))+'('+CAST(C.max_length AS varchar(8))+')') else

case when rtrim(ltrim(https://www.360docs.net/doc/e912191272.html,))in

('uniqueidentifier','datetime','smalldatetime','bit','text','ntext',' tinyint','smallint','int','integer','bigint') then rtrim(ltrim(https://www.360docs.net/doc/e912191272.html,)) else

case when rtrim(ltrim(https://www.360docs.net/doc/e912191272.html,))in ('money','decimal','numeric','float') then (rtrim(ltrim(https://www.360docs.net/doc/e912191272.html,))+'('+CAST(C.precision AS

varchar(8))+','+CAST(C.scale AS varchar(8))+')') else

'NOxTYPE'

end

end),

-----------------------*/

[Type]=https://www.360docs.net/doc/e912191272.html,,

[Length]=CAST(C.max_length AS varchar(8)),

[Precision]=CAST(C.precision AS varchar(8)),

[Scale]=CAST(C.scale AS varchar(8))

[xPrimaryKey]=ISNULL(IDX.PrimaryKey,N''),

[xDefault]=ISNULL(D.definition,N''),

[xNullAble]=Case When C.is_nullable=1 then N'Y' else N'' end,

[xColumnDesc]=ISNULL(PFD.[value],N''),

[xAccess1]=N'I',

[xAccess2]=N'I/O',

[xAccess3]=N'O'

into tb002

from [sys].[columns] C

Inner Join [sys].[objects] O

on C.[object_id]=O.[object_id] and O.type='U' and

O.is_ms_shipped=0

Inner Join [sys].[types] T

on https://www.360docs.net/doc/e912191272.html,er_type_id=https://www.360docs.net/doc/e912191272.html,er_type_id

Left Join [sys].[default_constraints] D

on C.[object_id]=D.parent_object_id and

C.column_id=

D.parent_column_id and C.default_object_id=D.[object_id] Left Join [sys].[extended_properties] PFD

on PFD.class=1 and C.[object_id]=PFD.major_id and

C.column_id=PF

D.minor_id

----and https://www.360docs.net/doc/e912191272.html,='Caption'

Left Join [sys].[extended_properties] PTB

on PTB.class=1 and PTB.minor_id=0 and C.[object_id]=PTB.major_id ----and https://www.360docs.net/doc/e912191272.html,='Caption'

LEFT JOIN

(

select IDXC.[object_id],IDXC.column_id,

Sort=CASE

IndexKey_Property(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id ,'IsDescending')

WHEN 1 then 'DESC' WHEN 0 then 'ASC' else '' end,

PrimaryKey=Case When IDX.is_primary_key=1 then N'PK' else N'' end, IndexName=https://www.360docs.net/doc/e912191272.html,

from [sys].[indexes] IDX

Inner Join [sys].[index_columns] IDXC

on IDX.[object_id]=IDXC.[object_id] and

IDX.index_id=IDXC.index_id

Left Join [sys].[key_constraints] KC

on IDX.[object_id]=KC.[parent_object_id] and

IDX.index_id=KC.unique_index_id

Inner Join

(

select [object_id],Column_id,index_id=min(index_id)

from [sys].[index_columns] group by [object_id],Column_id

) IDXCUQ

on IDXC.[object_id]=IDXCUQ.[object_id] and

IDXC.Column_id=IDXCUQ.Column_id and IDXC.index_id=IDXCUQ.index_id

) IDX

on C.[object_id]=IDX.[object_id] and C.column_id=IDX.column_id where 1=1 and https://www.360docs.net/doc/e912191272.html, in ('bms_month_sales')

select

xTableName,xColumn_id,xColumnName,xCaption,xType,xPrimaryKey,xDefault ,xNullAble,xColumnDesc,xAccess1,xAccess2,xAccess3 from

(

select

xTableName,xodid,xColumn_id,xColumnName,xCaption,xType,xPrimaryKey,xD efault,xNullAble,xColumnDesc,xAccess1,xAccess2,xAccess3 from tb001 union

select

xTableName,xodid,xColumn_id,xColumnName,xCaption,xType,xPrimaryKey,xD efault,xNullAble,xColumnDesc,xAccess1,xAccess2,xAccess3 from tb002 ) X

order by xodid

----------------------------------------------------------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[tb001]') and ObjectProperty(id, N'IsUserTable') = 1) drop table [tb001]

if exists (select * from dbo.sysobjects where id = object_id(N'[tb002]') and ObjectProperty(id, N'IsUserTable') = 1) drop table [tb002]