2

So let us say you have a string:

set @string = 'aaa,2,dqw,3,asdad,5,4'

I would like to read the chars that are after a char and a "," So the result to this string would be:

 Result
--------
  2
  3
  5

How could I do this?is there a way to use CHARINDEX for this?

John Pietrar
  • 513
  • 7
  • 19
  • 1
    Check [here](http://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string). – Blank Jul 08 '16 at 06:04
  • This is helpfull but there is a catch,as you can see in my example I don't want to extract all numbers for example after `asdad` I want to extract only the value `5` not the `4` also – John Pietrar Jul 08 '16 at 06:08
  • Ideally, don't reach this situation in the first place. Distinct data ought to be stored in distinct places, not all shoved together into a string that you then have to untangle. Can this not be fixed upstream to avoid becoming this string in the first place? – Damien_The_Unbeliever Jul 08 '16 at 06:28
  • 1
    @Damien_The_Unbeliever true, but one cannot always guarantee this. It might have come from a CSV file, in which case it may be junk data to you, but perhaps it was not to the designer. – clifton_h Jul 08 '16 at 06:31
  • 2
    @clifton_h - if it's coming from a CSV file, then you make sure you break it apart into separate columns during the process of importing the data into SQL, rather than letting it linger in a single text column. – Damien_The_Unbeliever Jul 08 '16 at 06:32
  • 2
    @Damien_The_Unbeliever I know, what you say is true. Always solve the underlying problem first. But sometimes life gives you lemons...or input boxes for customers to write stuff in. – clifton_h Jul 08 '16 at 06:34

2 Answers2

3

If your string is just like your example, using Charindex(',', <string>) works too. Otherwise, use PATINDEX. It functions similarly,but you can also set it to recognize all numeric characters.

IF PATINDEX('%[0-9]%', @String') <> 0
    THEN BEGIN
             SET @string = SUBSTRING(@string, PATINDEX('%[0-9]%', @string), LEN(@string) )
             SET @var = SUBSTRING(@string, PATINDEX('%[^0-9]%', @string) )
             END

you now can do as you please with those variables.

To your exception, just use one more case statement and rid of it. No reason the code has to be too complicated.

clifton_h
  • 1,298
  • 8
  • 10
0

One more Approach using numbers table and split string functions

declare @string varchar(max)
set @string = 'aaa,2,dqw,3,asdad,5,4'

;with cte
as
(
select * from [dbo].[SplitStrings_Numbers](@string,',')
)
select 
* from cte where isnumeric(item)=1

Output:
2
3
5
4

if you are sure about no special characters in your data..You can use above,,but some times using NUMERIC tends to show some characters as numbers

SELECT 
 ISNUMERIC('123') as '123'  --1
 ,ISNUMERIC('.') as '.' --Period ---1
 ,ISNUMERIC(',') as ',' --Comma ---1

In this case,you can use TRY_Parse available from SQL server 2012..

declare @string varchar(max)
    set @string = 'aaa,2,dqw,3,asdad,5,4'

    ;with cte
    as
    (
    select b.* from [dbo].[SplitStrings_Numbers](@string,',') a
    cross apply
    (select try_parse(a.item as int) ) b(val)
    )
    select 
    * from cte where val is not null
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94