SQL - parse string elements - SQL Server 2005

Posted on 2011-04-26
Last Modified: 2012-05-11
Hello experts,

I have a table with column file_name, and I am trying to split out the data into a new column before the hyphen.

table: person
person_id              file_name                mrn
12345                    12345-12.jpg           null
12346                    12346-34.jpg           null

I am trying to end up with:

table: person
person_id              file_name                mrn
32423                    12345-12.jpg           12345
72223                     12346-34.jpg          12345

Something like:

update person
set mrn = replace(file_name,RIGHT(file_name,charindex('-',reverse(file_name))-1),'')

but I get the error:

Msg 536, Level 16, State 2, Line 1
Invalid length parameter passed to the RIGHT function.
The statement has been terminated.


Question by:robthomas09
    LVL 24

    Accepted Solution

    You can use Left and CharIndex:
    update person
    set mrn = LEFT(file_name,CHARINDEX('-',file_name)-1)
    LVL 24

    Assisted Solution

    or you can use SubString:
    update person
    set mrn = SUBSTRING(file_name,1,CHARINDEX('-',file_name)-1)
    LVL 24

    Assisted Solution

    If you still want to correct your statement by using right:
    update person
    set mrn = replace(file_name, RIGHT(file_name,charindex('-',reverse(file_name)-1)),'')
    LVL 21

    Assisted Solution

    by:Alpesh Patel
    Select replace('12345-12.jpg',RIGHT('12345-12.jpg',charindex('-',reverse('12345-12.jpg'))),'')
    LVL 32

    Assisted Solution

    use this
    update person
    set mrn = case 
                 when charindex('-', file_name) > 0 then 
                   LEFT(file_name, charindex('-', file_name)-1)

    Open in new window


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    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…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 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

    18 Experts available now in Live!

    Get 1:1 Help Now