-4

I have following text Banker's XXXX YYYY~#0018800~MMMMM~0401 from sql table i need to filter only 0018800 from the text in select query how can i do it? DBMS is SQL Server

Darren
  • 68,902
  • 24
  • 138
  • 144
Khan
  • 5,052
  • 5
  • 22
  • 23
  • 6
    Solution strictly depends on concrete DBMS you're using. Anyway, there were dozens of similar questions for almost every existed DBMS at StackOverflow, just use search. Here one possible solution: [How do I split a string so I can access item x?](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) – Andrey Korneyev Feb 26 '16 at 06:54

2 Answers2

1

As Andy Korneyev has pointed out, this would entirely depend on the DBMS.

For SQL Server you could use CHARINDEX and do something like the following:

DECLARE @Str VARCHAR(120)

SET @Str = 'XXXX YYYY~#0018800~MMMMM~0401'

SELECT SUBSTRING(@Str, CHARINDEX('#', @Str)+1, CHARINDEX('~', @Str)-3)

Or for MySQL you could use SUBSTRING_INDEX

Darren
  • 68,902
  • 24
  • 138
  • 144
0

Try this

declare @test nvarchar(max) ='XXXX YYYY~#0018800~MMMMM~0401'

declare @index1 int = (charindex( '#', @test )+1);
declare @firstvariable nvarchar(max) =  substring( @test, @index1 ,LEN(@test) )

declare @index2 int = (charindex( '~', @test )-2);
select @index1,@firstvariable,@index2,substring( @firstvariable, 0,@index2 )
SimarjeetSingh Panghlia
  • 2,200
  • 13
  • 14