Link to home
Start Free TrialLog in
Avatar of DarrenJackson
DarrenJacksonFlag for United Kingdom of Great Britain and Northern Ireland

asked on

T-Sql Instr query

Guys I am trying to use the instr function in Microsoft SQL 2008 using t-sql im a bit rusty and struggling with this. I need to split up the following using the "-" as a splitter

WRKP-PRNT-WNDW

1st column would show
WRKP
Then second would show
PRNT
3rd column would show
WNDW

but there may be more than 3 potentially 4 or 5

Can you assist with the T-SQL

Regards
Avatar of DarrenJackson
DarrenJackson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

And to add to this sometimes there is just 1 value

like

WRKP

so it would need to just return this in the first column
Ive got this but it only returns the first value and it doesnt return any values if there is only 1 entry

LEFT(SUBSTRING(a.probcode + '-', CHARINDEX(' ', a.probcode + '-') +1, LEN(a.probcode + '-')),
              CHARINDEX('-', SUBSTRING(a.probcode+ '-',
              CHARINDEX('-', a.probcode + '-') +2, LEN(a.probcode + '-')))) as test,
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
can you give more details. it lookes like you have column  probcode. this column has data something like this WRKP-PRNT-WNDW

and result should look lkie this

WRKP PRNT WNDW

you want them to select them as column in your select query

anillucky31  I would like them in separate columns

Thanks
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
will PARSENAME function work on string which has more than 4 parts? As far as i know it will not work if there are more than 3 dots in string.  please correct me if i am wrong.
>> please correct me if i am wrong. <<
You are absolutely right.  I overlooked the requirement for 5 parts.
Guys thanks for the help anillucky3 your suggestion is spot on this does the trick acperkins your suggestion also work but as per the other posts I belive there is a limitation in this.

I want to award you both points for helping me

Regards