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
mousemat24Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Update Book
set Book_id = SUBSTRING (url , charindex('?unid', url)+6 , charindex('&id', url)-1 - charindex('?unid', url)-5 )
0
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.