We help IT Professionals succeed at work.

SQL - parse string elements - SQL Server 2005

Medium Priority
1,066 Views
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.

Thoughts?

Thanks!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.