?
Solved

extract 5-digits substring from string in T-SQL / pattern matching

Posted on 2013-01-08
8
Medium Priority
?
1,820 Views
Last Modified: 2013-01-08
Dear Experts,

I wonder if such text operations / pattern matching are easily available in T-SQL.

select fivedigits(text_field) from my table.

fivedigits is the magic function I'm looking for.

Sample input and output:
Input "504-A34BC-322-2232" output: null
Input "504-A34BC-22325" output: 22325

Input string will always contain only one 5-digits substring.

thanks
Jarek
0
Comment
Question by:ja-rek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38757049
Sounds like you need a regular expression match for 5 digits.

From what I'm reading (haven't had to do this myself), you need to use CLR integration to make it work.

Read these three articles:
http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server-2005-2008
http://stackoverflow.com/questions/1964124/regular-expression-inside-sql-server
http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Looks like your regular expression match for 5 digits would be \d{5}   (but I'm not exactly an expert with regexps)
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38757071
try

Declare @str varchar(50)

set @str ='504-A34BC-22325'

select case when  isnumeric (replace(right(@str,5),'-','a'))=1 then right(@str,5)
else NULL end reslt

Open in new window

0
 
LVL 1

Author Comment

by:ja-rek
ID: 38757084
sjwales: thanks, I will read these articles if I don't get ready solution
EugeneZ: sorry, this is not universal enough, I need regular expressions
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 38757168
if you need universal -- you can try to use regular expressions
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1200 total points
ID: 38757377
This would do it for your specific example:
SELECT  YourColumnName
FROM    YourTableName
WHERE   PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', YourColumnName) > 0

Open in new window

0
 
LVL 39

Assisted Solution

by:appari
appari earned 800 total points
ID: 38757425
create a UDF and use it as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION GetFiveDigitSubString 
(
	@src varchar(50)
)
RETURNS varchar(5)
AS
BEGIN
	DECLARE @retVal varchar(5)
	declare @srcLen int
    declare @curPos int

    if @src is null 
          return null

	select @srcLen = datalength(@src), @curPos = 1, @retVal=''
    While @curPos <= @srcLen
	begin
		if substring(@src,@curpos,1) like '[0-9]'
			select @retVal=@retVal + substring(@src,@curpos,1)
		else
			select @retVal=''
		if datalength(@retVal)=5
			return @retVal
		Select @curPos = @curPos + 1
	end
		if datalength(@retVal)=5
			return @retVal
		--else 
			return null
END
GO

Open in new window


use it as follows:
select dbo.GetFiveDigitSubString(colName) from tableName
0
 
LVL 1

Author Closing Comment

by:ja-rek
ID: 38757436
Many thanks for help!
0
 
LVL 39

Expert Comment

by:appari
ID: 38757451
I was thinking too much, we can get the result by using patindex and substring functions as suggested by acperkins

select
case when patindex('%[0-9][0-9][0-9][0-9][0-9]%', colName) > 0
then substring(colName,patindex('%[0-9][0-9][0-9][0-9][0-9]%', col1),5) else null end
from tablename
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question