Parsing a string - Charindex? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Parsing a string - Charindex?

Author

Topic

troopersma
Starting Member

2 Posts

Posted - 2008-07-15 : 23:04:50

I have a field in the table that is called "episode". This episode field may contain multiple values. All values start with an alpha followed by two or three numbers. So, for example, one might look like "A03F31N290".

What I want to be able to do is pull each of those values out but I'm not sure how to do it? I thought maybe I could use Charindex but I think I can only look for one value (not all alpha characters).

Any insight on how I might be able to accomplish this task?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 00:53:58

are you trying to extract only the numbers? then this might help

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-16 : 08:45:41

If you can, after parsing, be sure to store this data properly in a normalized format. This is a really bad way to store data and as you can see it makes things very difficult to work with!

Your best bet for parsing is unfortunately to loop through the string, character by character, and detect if it is a numeric or a letter. You can use a User Defined Function to do this.

- Jeff
http://weblogs.sqlteam.com/JeffS

troopersma
Starting Member

2 Posts

Posted - 2008-07-18 : 10:28:11

No, I'm not trying to extract just the numbers. I need to extract each value.

Let me give more detail. This field is basically a denial code. Each week on Saturday (aren't I lucky?) I have to run a process that gives all of our providers a list of denied/accepted/etc claims. Part of this is that I first have to run a query selecting this "episode" field. As I said, in it it contains "A03F31N290" then the next one may contain "F31N290N43". So after I run the basic select I go through and manually remove all of the duplicates and create a single list of values "A03,F31,N290,N43" (except there are usually closer to 20 values total when I am done).

Next I take this list of "episodes" and plug them into an exec statement that runs the process. I can only load four episodes as variables at a time. So it means sitting there, typing in the four episodes and running that exec statement five times if I have 20 episodes.

I'm an analyst with some SQL experience (about a year of minor programming). I know there has to be a better way to do this that would cut my time down from the current 3 hours that I have to work every Saturday to do the process! I have programmed in other languages and what I'm envisioning is stripping the episode values to a temp table of some sort so then they can be sorted and loaded into the exec statement as variables. But I have no clue how to do that either. Thought I'd start with the basic job of getting the data OUT of that field for starters!

To Jeff, I totally agree but I'm not in a position where they'd listen to me about how to store their data. Can you share more about the "User Defined Function" to loop through and check each character for number vs letter?

I'm open to suggestions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 10:52:44

make a UDF like this

CREATE FUNCTION GetValueArray
(@String  varchar(8000)
)
RETURNS @Result table
(ID int identity(1,1),
Val varchar(200)
)
AS
BEGIN
DECLARE @ExtVal varchar(200)

WHILE @String IS NOT NULL
BEGIN
SELECT @ExtVal=
    CASE WHEN PATINDEX('%[A-Z]%',SUBSTRING(@String,2,LEN(@String)))>0
     THEN LEFT(@String,PATINDEX('%[A-Z]%',SUBSTRING(@String,2,LEN(@String))))
     ELSE @String
     END,
     @String=CASE WHEN PATINDEX('%[A-Z]%',SUBSTRING(@String,2,LEN(@String)))>0
     THEN SUBSTRING(@String,PATINDEX('%[A-Z]%',SUBSTRING(@String,2,LEN(@String)))+1,LEN(@String))
     ELSE NULL
     END

INSERT INTO @Result (Val)
SELECT @ExtVal
END
RETURN
END

and then use

SELECT * FROM dbo.GetValueArray(yourstringvalue)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-07-21 : 13:07:58

or using a numbers table...


Declare @t Table (code varchar(100))
Insert Into @t Select 'A03F31N290'
Insert Into @t Select 'F31N290N43'

Declare @n Table (n int)
While (coalesce((Select max(n) From @n),1) < 26)
Begin
	Insert Into @n Select coalesce((Select max(n) From @n),0)+1
End

Select * From @t

Select 
	Z.Code,
	substring(Z.Code,Z.n,min(coalesce(Y.n,len(Z.Code)+1))-Z.n)
From
	(
	Select * 
	From @t A
	Cross Join @n B
	Where substring(A.code,B.n,1) like '[A-Z]'
	) Z
Left Join
	(
	Select * 
	From @t A
	Cross Join @n B
	Where substring(A.code,B.n,1) like '[A-Z]'
	) Y
On Z.code = Y.code
and Z.n < Y.n
Group By Z.Code, Z.n
Order By Z.Code, Z.n

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources