SQL string iteration function

I have the following values in a table which I need to extract the numerical value combinations. All the values follow the same pattern with underscores.
Can I pls have some help to create a simple function that will loop thru and return the 2 sets of numbers from the passed string being 1234 and 5678

Who is Participating?
Surendra NathConnect With a Mentor Technology LeadCommented:
give it a whirl

create function dbo.slice
 @t varchar(1000)
returns @slicedRows Table
  NumbericalPart    BIGINT
declare @first_ int 
declare @second_ int 
declare @third_ int 

set @first_ = charindex('_',@t)
set @second_ = charindex('_',@t,@first_ + 1 )
set @third_ = charindex('_',@t,@second_ + 1 )
insert into  @slicedRows 
select CAST(substring(@t,@first_+1,@second_- @first_-1) as BIGINT)
select CAST(substring(@t,@second_+1,@third_-@second_-1) as BIGINT)

Open in new window

the values are returned when you do the below

select * from dbo.slice('A-FP_1234_5678_EF')

Open in new window

Remember the function does return a table so, you need to do a cross apply if want to join it in a where clause.
There may be a better way but here's what I'm thinking...  If your data always follows that format then you have three underscore characters that you need to know the positions of.

Declare @POS1 int  //position of the first _
Declare @POS2 int  //position of the second _
Declare @POS3 int  //position of the third _
Declare @NumberSet1 varchar(10)  //First string of #'s
Declare @NumberSet2 varchar(10)  //Second string of #'s

// You can use something other than varchar in the last two... i don't know your data...

// The next three lines will set the positions of the _ chars into the three variables

Set @POS1 = CharIndex("_", string, 1)
Set @POS2 = CharIndex("_", string, @POS1+1)
Set @POS3 = CharIndex("_", string, @POS2+1)

// Now that you know where the _ characters are you can use substring to capture them

Set @NumberSet1 = SubString(string, (@POS1+1), (@POS2 - @POS1 - 1))
Set @NumberSet2 = SubString(string, (@POS2 + 1), (@POS3 - @POS2 - 1))

Hope it helps...
CraigLazarAuthor Commented:
Thanks Neo, perfect, just made some adjustements, returning BIGINT instead of table and created 2 functions each for number 1 and number 2
All Courses

From novice to tech pro — start learning today.