Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BrewinAZ
Starting Member
2 Posts |
 Posted - 2014-04-03 : 12:11:05
|
| Hi all here's my issue. below is an example of my data my users want to see the dose which is the first number before dose size and quantity if there is any which is the number before the dash in sepearte columns. I can get the first piece but not the second any ideas on a combination of functions that can get me there. I have the following but not sure how to get the quantity.select reverse(left(Reverse('METOPROLOL & HYDROCHLOROTHIAZIDE TAB 100-50 MG'), CHARINDEX('-', REVERSE('METOPROLOL & HYDROCHLOROTHIAZIDE TAB 100-50 MG')) - 1))BETAXOLOL HCL TAB 20 MG METOPROLOL & HYDROCHLOROTHIAZIDE TAB 100-50 MG PYRILAMINE TAN-PHENYLEPH TAN SUSP 30-5 MG/5ML |
|
|
Rekonn
Starting Member
7 Posts |
Posted - 2014-04-03 : 12:39:53
|
| I'm sure someone will come up with something better, but here's one to get started. This will only work for those strings with a -.declare @str varchar(250)set @str = 'METOPROLOL & HYDROCHLOROTHIAZIDE TAB 100-50 MG'select reverse(LEFT( SUBSTRING(reverse(@str) , patindex('%-%', reverse(@str)) + 1 , LEN(@str)) , patindex('% %', SUBSTRING(reverse(@str) , patindex('%-%', reverse(@str)) + 1 , LEN(@str)) ) -1)) |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-04-03 : 16:19:33
|
| What output do you expect to see from each of these inputs?:BETAXOLOL HCL TAB 20 MG METOPROLOL & HYDROCHLOROTHIAZIDE TAB 100-50 MG PYRILAMINE TAN-PHENYLEPH TAN SUSP 30-5 MG/5ML |
|
|
|
BrewinAZ
Starting Member
2 Posts |
Posted - 2014-04-03 : 16:43:23
|
Hi Looking to see Description, quantity, dose, type columns. problem is not all have quantity like the BETAXOLOL while the MET has 100 quote:
Originally posted by ScottPletcher
What output do you expect to see from each of these inputs?:BETAXOLOL HCL TAB 20 MG METOPROLOL & HYDROCHLOROTHIAZIDE TAB 100-50 MG PYRILAMINE TAN-PHENYLEPH TAN SUSP 30-5 MG/5ML
|
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-04-04 : 10:28:19
|
| Again, what specifically should be in each column of results from those input values? What specific values should be in the "dose" column, etc.? You must remember, I'm a DBA, not a medical person. |
|
|
|
|
|
|
|
|
Get part of string