CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
M.Tajari
M.Tajari

Reputation: 59

Select a specific number from a row in sql query

I want to select a SQL Query for only "09xxxxxxxxx" from below table :

021-77083709 - 09125800327

09123301673 22514057

09121964217 09332712035

Its might be anywhere in the row and I want to select these numbers. This mean "09" + 9 character after that.

For Example in overhead rows, select these numbers :

09125800327

09123301673

09121964217

09332712035

My Table http://upload.tehran98.com/upme/uploads/33a59e81d6d51d311.jpg

Thanks

Upvotes: 0

Views: 2584

Answers (1)

Austin
Austin

Reputation: 904

This will get you part of the way there...

SELECT  SUBSTRING(tbl.columnName,
                  PATINDEX('%09[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',
                           tbl.ColumnName), 11) FirstOccurrance
FROM    TableName tbl
WHERE   tbl.columnName LIKE '%09[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

This will only extract the first occurrence of the pattern within a column, also the pattern matching is pretty limited.

By the way in your example is 09 + 9 characters not 10

Here is a function that can be used to extract all matching values in a column as a csv string

CREATE FUNCTION dbo.FilterValues
    (
      @OriginalValue NVARCHAR(2000)
    )
RETURNS NVARCHAR(2000)
AS 
    BEGIN
        DECLARE @IDX INT = PATINDEX('%09[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',
                                    @OriginalValue);
        DECLARE @Temp NVARCHAR(4000) = @OriginalValue;
        DECLARE @Result NVARCHAR(4000) = '';

        WHILE ( @IDX IS NOT NULL
                AND @IDX > 0
              ) 
            BEGIN
                SET @Result = @Result + SUBSTRING(@Temp, @IDX, 11);
                SET @Temp = SUBSTRING(@Temp, @IDX + 11, LEN(@Temp) - 11);
                SET @IDX = PATINDEX('%09[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',
                                    @Temp);
                IF ( @IDX IS NOT NULL
                     AND @IDX > 0
                   ) 
                    BEGIN
                        SET @Result = @Result + ',';
                    END

            END

        RETURN @Result;
    END

Here is an example on how to use the query with the function

SELECT  dbo.FilterValues(tbl.ColumnName) MatchedValues
FROM    TableName tbl
WHERE   tbl.columnName LIKE '%09[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

Upvotes: 1

Related Questions