How to print or select specific rows in SQL-CodePudding

I want to print only the data that starts with a number from a SQL string column. Right now its printing complete string (see image). I only want row 2 and 4 as it contains the data starting with a number. Here is the sql code:

DECLARE @string2 VARCHAR(MAX) 
SET @string2 ='DOB;04 Mar 1199;passport;1234567'
DECLARE @SEP CHAR(1) 
SET @SEP=';'
select @string2
SELECT value
FROM   STRING_SPLIT(@string2, @sep)

CodePudding user response：

You could extract the first 2 characters and check if they're NUMERIC

DECLARE @string2 VARCHAR(MAX) 
DECLARE @SEP CHAR(1) 

SET @string2 ='DOB;04 Mar 1199;passport;1234567'
SET @SEP=';'

SELECT [value] FROM 
(
    SELECT value
    FROM   STRING_SPLIT(@string2, @sep)
) a
WHERE IsNumeric(left([value],2)) = 1

CodePudding user response：

You can use the value in the where clause. So using the SUBSTRING and the ISNUMERIC functions should solve your problem.

CodePudding user response：

you can write a query with using