September 21, 2011 at 9:33 am
Hi,
Is there a way that I can extract only the numerical part of the string. I used charindex, substring,left but looks like I am missing something here. All fields have "TEST" in it
data format is -
ABC:123;TEST:pq
DEF:1234;TEST:mn
DEF:12;TEST:uv
thanks
September 21, 2011 at 9:44 am
DECLARE @a VARCHAR(50)
SET @a = 'ABC:123;TEST:pq'
SELECT PATINDEX('%:%', @a) AS Col, PATINDEX('%;%', @a) AS SCol, SUBSTRING(@a, 4 + 1, 8 - (4 + 1)) AS Extract
DECLARE @b-2 table (a varchar(50))
INSERT INTO @b-2 (
a
)
SELECT 'ABC:123;TEST:pq' UNION ALL
SELECT 'DEF:1234;TEST:mn' UNION ALL
SELECT 'DEF:12;TEST:uv'
SELECT
a
, SUBSTRING(a , Col + 1 , SCol - ( Col + 1 )) AS Extract
FROM
(
SELECT
PATINDEX('%:%' , a) AS Col
, PATINDEX('%;%' , a) AS SCol
, a
FROM
) dtPos
September 22, 2011 at 4:30 am
Try the below code and let me know in case of any erorrs or problems.
This will extract the numbers from the alphanumeric values.
The final output is of Varchar type , convert it to int
DECLARE @string_in VARCHAR(100)
SELECT @string_in = 'ABC:123;TEST:pq'
DECLARE @sub CHAR
DECLARE @num VARCHAR(100)
SELECT @num = ''
WHILE ( Patindex('%[0-9]%', @string_in) > 0
AND Len(@string_in) > 0 )
BEGIN
SET @sub = Substring(@string_in, Patindex('%[0-9]%', @string_in), 1)
SELECT @num = @num + @sub
SET @string_in = RIGHT(@string_in, Len(@string_in) - Patindex('%[0-9]%',
@string_in))
END
SELECT @num
SELECT CAST( @num as int )
thanks
sarat 🙂
Curious about SQL
September 22, 2011 at 8:24 am
Thanks everyone. greatly appreciate that.
I have some confusion here , I have a table variable which first brings these strings after joining to different tables..
in other words If I have @table that has a column "mystring" and this column contains all such strings...basically I need to insert these "mystring" column values to your code. how do i do that ....
September 22, 2011 at 8:26 am
SQL_Nw (9/22/2011)
Thanks everyone. greatly appreciate that.I have some confusion here , I have a table variable which first brings these strings after joining to different tables..
in other words If I have @table that has a column "mystring" and this column contains all such strings...basically I need to insert these "mystring" column values to your code. how do i do that ....
Please post what you have so far.
September 22, 2011 at 9:14 am
DECLARE @item table
(item_id int,
ITEM_desc varchar(100),
)
insert into @item
select item_id,
ITEM_desc
from ITEMS
where ITEM_desc like 'test%'
These item_desc fields (strings) from @item , I want to insert in the code you gave and break it and extract only the numerical portion.
September 22, 2011 at 9:21 am
SELECT
ITEM_desc
, SUBSTRING(ITEM_desc , Col + 1 , SCol - ( Col + 1 )) AS Extract
, YourOtherColumns
FROM
(
SELECT
PATINDEX('%:%' , ITEM_desc) AS Col
, PATINDEX('%;%' , ITEM_desc) AS SCol
, ITEM_desc
, YourOtherColumns
FROM
@item
) dtPos
September 22, 2011 at 9:34 am
Thanks , I just tried it.
but I I ran into error,
Invalid length parameter passed to the SUBSTRING function.
Is there something else that needs to be added. sorry I haven't worked with complex strings before.
I just used the piece of code you sent right after my @item table variable.
September 22, 2011 at 9:42 am
Run only the inner part. Find what strings have a 0 for either patindex, especially the 2nd.
You might want to put a filter or case statement on that.
September 22, 2011 at 11:59 am
This is what you are looking for
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server
Hope that helps
September 23, 2011 at 7:33 am
This forum is awesome. Thanks everyone. It has been great help. I am able to extract the numeric portion.
Now , I need to extract the part after 'TEST:'
***if there is ; after 'TEST:' then part between : and ; ( example 'BC' from TEST:BC;)
***if there is no ; after 'TEST:' then part after 'TEST:' ( example 'BCD' from TEST:BCD and 'BC' from ABC:1234;TEST:BC
)
the number of characters after 'TEST:' can vary
the data could be in this format:
ABC:1234;TEST:BC
ABC:1234;TEST:BCDE
TEST:BC;
TEST:BCD
TEST:BC;ABC:1234
is there a built in function for that?
September 23, 2011 at 7:38 am
Use this to split the string into rows, then you can just take everything right of the next splitting character
September 23, 2011 at 9:15 am
Thank you. I am going to test it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply