Help with SQL substring and updates

Posted on 2009-12-22
Last Modified: 2012-05-11
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

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
Question by:mousemat24
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    declare @Url varchar(1000) =''
    select SUBSTRING (@url , charindex('?unid', @url)+6 , charindex('&id', @url)-1 - charindex('?unid', @url)-5 )

    Author Comment

    How do you put that into a update statement, thanks
    LVL 75

    Accepted Solution

    Update Book
    set Book_id = SUBSTRING (url , charindex('?unid', url)+6 , charindex('&id', url)-1 - charindex('?unid', url)-5 )

    Author Comment

    thanks aneeshattingal!!!!
    LVL 51

    Expert Comment

    try this if you still could not find the solution

    update book
    Book_id = SUBSTRING (url, charindex('?unid=', url)+6, charindex('&id', url)- charindex('?unid=', url)-6)

    Author Comment

    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

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now