- Data loading create table #t (column1 varchar (20)) Insert #t select '040011'Union all select' 024 integrated 'union all select' 021 I don't know 'union all select' 031 is not ' Union all select 'Not 3'Union All Select' knows'
--1: Request to include numbers, and length of 6 (including Chinese)
Select * from #t where column1 limited '% [0-9]%' and len (colorn1) = 6
- Results Column1 -------------------- 040011010021021 I don't know
(The number of rows affects is 3 lines)
--2: Ask for a collection of Chinese, and the length is 6 (including numbers)
Select * from #t where column1 limited '% [^ 0-9]%' and len (colorn1) = 6
- Results Column1 -------------------- 021 I don't know
(The number of rows affects is 1 line)
--3: Ask for a collection of numbers, and the length of 6 (not included in Chinese)
Select * from #t where color [0-9]% 'and len (colorn1) = 6 - Results Column1 -------------------- 040011010021
(The number of rows affects is 2 lines)
--4 Seeking all records only with Chinese records
Select * from #t where column1 not limited '% [0-9]%'
--result
COLUMN1 -------------------- know
(The number of rows affects is 1 line)
--5. Seeking all records only have a number of records
Select * from #t where column1 not limited '% [^ 0-9]%'
COLUMN1 -------------------- 040011010021
(The number of rows affects is 2 lines) or
Select * from #t where isnumeric (color "= 1
--result
COLUMN1 -------------------- 040011010021
--6. The record in the query data contains Chinese
SELECT CASE WHEN COLUMN1 LIKE N '% [Ah]%' Ten 'contains Chinese' Else 'does not include Chinese' endfrom #t
--result
---------- Does not contain Chinese does not include Chinese contains Chinese containing Chinese contains Chinese to include Chinese
(The number of rows affected is 7 lines)
--7: Just query the numeric field value
Select * from #twhere Patindex ('% [^ - ^ 0-9]%', Column1) = 0
--result
COLUMN1 -------------------- 040011010021
(The number of rows affects is 2 lines)
--8: Just query Chinese field value Select * from #t where pattern ('% [^ - ^ ah - seat]%', column1) = 0 - Results
COLUMN1 -------------------- know
(The number of rows affects is 1 line)
