SQL Server sql repeat regex pattern unlimited times

ugmeyewa 于 2023-05-05 发布在其他

关注(0)|答案(4)|浏览(191)

I need to select where column contains numbers only and ends with a hyphen

I'm running SQL Server Management Studio v17.9.1

I have tried:

select * from [table] where [column] like '[0-9]*-'
select * from [table] where [column] like '[0-9]{1,}-'
select * from [table] where [column] like '[0-9]{1,2}-'

none of these work. The expression ([0-9]*-) works in any regex tester I've run it against, SQL just doesn't like it, nor the other variations I've found searching.

sql-server

来源：https://stackoverflow.com/questions/56838707/sql-repeat-regex-pattern-unlimited-times

4条答案

按热度按时间

6gpjuf901#

You can filter where any but the last character are not numbers and the last is a dash. DATALENGTH/2 assumes NVARCHAR type. If you're using VARCHAR, just use DATALENGTH

SELECT
    * 
FROM 
    [table]
WHERE 
    [column] like '%-'
    AND
    LEFT([column], (datalength([column])/2)-1) NOT LIKE '%[^0-9]%'

赞(0）回复(0）举报 2023-05-05

z9gpfhce2#

SQL Server does not support regular expressions -- just very limited extensions to like functionality.

One method is:

where column like '%-' and
      column not like '%[^0-9]%-'

赞(0）回复(0）举报 2023-05-05

vngu2lb83#

You can use left() and right() functions as below :

with [table]([column]) as
(
 select '1234-'  union all
 select '{123]'  union all
 select '1234'   union all
 select '/1234-' union all
 select 'test-'  union all
 select '1test-' union all
 select '700-' 
)
select *
  from [table] 
 where left([column],len([column])-1) not like '%[^0-9]%'
   and right([column],1)='-';

 column
 ------
 1234-
 700-

Demo

赞(0）回复(0）举报 2023-05-05

qnyhuwrf4#

WITH [table] AS (
        SELECT '-' as [column] UNION ALL
        SELECT '1-' as [column] UNION ALL
        SELECT '123-' as [column] UNION ALL
        SELECT '1234-' as [column] UNION ALL
        SELECT '123-4' as [column] UNION ALL
        SELECT '-1234' as [column] 
    )
    SELECT * FROM [table]
    WHERE [column] like '[0-9]' + REPLICATE('[0-9]', LEN([column])-2) + '-'

Return
1-
123-
1234-

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server sql repeat regex pattern unlimited times

4条答案

相关问题

热门标签

最新问答