• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

Help with SQL substring and updates

Hi there

Wonder if you can help me, I need an update statment where I need to take the number out from (?unid=332)a URL and place it into the BOOK_ID column

So this URL appears in the BOOK_URL field, I need to take the number out from ?unid=332
http://www.testing.com/testing/index.aspx?unid=332&id=1746&test=testers

and place it into the BOOK_ID field

so in this case the number would be 332, please note the number could be of any length, but it will always be a number

The table name is called Book, it has to be an update statement as well.

Thankyou kindly
0
mousemat24
Asked:
mousemat24
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @Url varchar(1000) ='http://www.testing.com/testing/index.aspx?unid=332&id=1746&test=testers'
select SUBSTRING (@url , charindex('?unid', @url)+6 , charindex('&id', @url)-1 - charindex('?unid', @url)-5 )
0
 
mousemat24Author Commented:
How do you put that into a update statement, thanks
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Update Book
set Book_id = SUBSTRING (url , charindex('?unid', url)+6 , charindex('&id', url)-1 - charindex('?unid', url)-5 )
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mousemat24Author Commented:
thanks aneeshattingal!!!!
0
 
HainKurtSr. System AnalystCommented:
try this if you still could not find the solution

update book
set
Book_id = SUBSTRING (url, charindex('?unid=', url)+6, charindex('&id', url)- charindex('?unid=', url)-6)
0
 
mousemat24Author Commented:
acaully looking at the code, this is assuming theres a &id, there might be times where there isnt an id, how does it work without an id?

I can tell you it will always be a integer number
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now