CraigLazar
asked on
SQL string iteration function
Morning,
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.
A-FP_1234_5678_EF
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
Thanks
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.
A-FP_1234_5678_EF
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
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Neo, perfect, just made some adjustements, returning BIGINT instead of table and created 2 functions each for number 1 and number 2
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...