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

SQL - parse string elements - SQL Server 2005

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!
0
robthomas09
Asked:
robthomas09
  • 3
5 Solutions
 
jimyXCommented:
You can use Left and CharIndex:
update person
set mrn = LEFT(file_name,CHARINDEX('-',file_name)-1)
0
 
jimyXCommented:
or you can use SubString:
update person
set mrn = SUBSTRING(file_name,1,CHARINDEX('-',file_name)-1)
0
 
jimyXCommented:
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)),'')
0
 
Alpesh PatelAssistant ConsultantCommented:
Select replace('12345-12.jpg',RIGHT('12345-12.jpg',charindex('-',reverse('12345-12.jpg'))),'')
0
 
Ephraim WangoyaCommented:
use this
update person
set mrn = case 
             when charindex('-', file_name) > 0 then 
               LEFT(file_name, charindex('-', file_name)-1)
             else
               ''
           end

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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