0

I have the following link: /ABCDEF/ABCDEF/ABC/8921/154535

I need to insert only the last 6 numbers i.e. 154535 in a column in a table.

halfer
  • 19,824
  • 17
  • 99
  • 186
Shaz
  • 3
  • 1
  • Which rdbms you use? – Giorgi Nakeuri Apr 29 '15 at 05:10
  • what have you done so far – Tharif Apr 29 '15 at 05:12
  • I tried using PAT Index, but I never used it before, so everything is going upside down ;) – Shaz Apr 29 '15 at 05:13
  • here your answer...... http://stackoverflow.com/questions/1722334/extract-only-right-most-n-letters-from-a-string select substr('TN0001234567890345',-10) from dual; – Narashi Hadiya Apr 29 '15 at 05:49
  • From where this link comes? Do you use .net language? Linq? – Maciej Los Apr 29 '15 at 06:23
  • Please always add the code you are using to your questions, so they are readable both for people who wish to answer, and for future readers who wish to learn. If you can do that here, even though the question is already answered, that would be great (the code you supplied in a comment to libisyne is helpful). – halfer May 01 '15 at 21:23

3 Answers3

3

Try below code:

Declare @s varchar(100) = '/ABCDEF/ABCDEF/ABC/8921/154535'
select REVERSE(SUBSTRING(REVERSE(@s),0,CHARINDEX('/',REVERSE(@s))))
Vikrant
  • 4,920
  • 17
  • 48
  • 72
libisyne
  • 42
  • 2
  • I can't hardcode the '/ABCDEF/ABCDEF/ABC/8921/154535' I have several of these in a table, and I need to do a select from that table to retrieve all of these :S When am doing this: Declare @s varchar(100) = (select DISTINCT landing_page from [dbo].[No_of_Views]), it's giving me an error- returned more than 1 query.. – Shaz Apr 29 '15 at 05:34
  • 1
    @Shaz - You should add such details in your question. if you need to get this from your table, use `select REVERSE(SUBSTRING(REVERSE(landing_page),0,CHARINDEX('/',REVERSE(landing_page)))) FROM [dbo].[No_of_Views]` – ughai Apr 29 '15 at 06:04
1

Try below code:

Declare @s varchar(100) = '/ABCDEF/ABCDEF/ABC/8921/154535'
select substring(@s, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]', @s), len(@s))
Vikrant
  • 4,920
  • 17
  • 48
  • 72
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • The above is working perfectly... The problem is i have these data in a table, where there are many links like this. I tried below code but it's not working: Declare @s varchar(100) = (select DISTINCT landing_page from [dbo].[No_of_Views]) --'/ABCDEF/ABCDEF/ABC/8921/154535' select substring(@s, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]', @s), len(@s)) from [dbo].[No_of_Views] – Shaz Apr 29 '15 at 05:26
1

You are assigning multiple rows to a variable. So, you get error : returned more than 1 query

Try below simple solution:

select DISTINCT REVERSE(SUBSTRING(REVERSE(@s),0,CHARINDEX('/',REVERSE(@s)))) from [dbo].[No_of_Views]

And if you want to insert then:

INSERT INTO table_name --your table name
select DISTINCT REVERSE(SUBSTRING(REVERSE(@s),0,CHARINDEX('/',REVERSE(@s)))) from [dbo].[No_of_Views]
Vikrant
  • 4,920
  • 17
  • 48
  • 72