0

I have a text field (field named "BiographyText" in the "Employs" table) in SQL Server 2008 which stores KPI target figures:

rebfirst60,
reifirst1.3,
retfirst50

The first target is 60, for RebFirst, the second is 1.3 for ReiFirst and 50 for the third, RetFirst.

I want to be able to return the 3 different numerical values, as these would be deemed the targets for each kpi for a certain employee.

I am having a complete mind block trying to figure out the best way to do this, any advice/help?

Overall I am trying to find the kpiname ("rebfirst") and then retrieve the next 2 characters/digits

I tried the following, but it errors on function 2 of the first substring, as it is non-numeric:

select SUBSTRING(biographytext,SUBSTRING('rebfirst',1,2),2) from employs

Thanks

gbn
  • 394,550
  • 75
  • 549
  • 647
Stuart1044
  • 404
  • 5
  • 16
  • 4
    Why not store as separate values and rows? Normalisation of a database exists to avoid this type of problem – gbn Apr 08 '13 at 10:45
  • Or was it simply formatting that makes it look like one value? – gbn Apr 08 '13 at 10:47
  • The employs table has one row for each employee and now where to store such targets as such, so as a workaround this text field is being used – Stuart1044 Apr 08 '13 at 10:47
  • @Stuart1044: why not create a new table? – gbn Apr 08 '13 at 10:49
  • Firstly, I would suggest a database refactor to normalize your data structure better, so that each of these 3 values were in their own columns (ideally in another table with a key that references back to this one). (suggestion only, more understanding of your current data model would be required for a more definitive recommendation) – DragonZero Apr 08 '13 at 10:50
  • as @gbn says, storing two logical attributes in one column is wrong. I prefer joins to string manipulation. Additionaly, the value will be typed so you would't need to convert/parse. – Jodrell Apr 08 '13 at 10:50
  • We are a bit stuck as the software UI would only update this core table (employs), so if new targets are updated via the UI they would be in the format above – Stuart1044 Apr 08 '13 at 10:54
  • I don't need the actual kpiname from this just the value – Stuart1044 Apr 08 '13 at 10:57
  • is the kpinname value of fixed length? – Ajo Koshy Apr 08 '13 at 11:00
  • Perhaps you could use any number of string splitting functions first. http://stackoverflow.com/questions/2647/split-string-in-sql – Nick.McDermaid Apr 08 '13 at 11:18

3 Answers3

2

Try this

SELECT 
    CASE WHEN PatIndex('%[a-z]%',REVERSE(BiographyText)) > 0
      THEN RIGHT(BiographyText,PatIndex('%[a-z]%',REVERSE(BiographyText))-1)
      ELSE '' END AS target 
FROM employs

and also check another solution using function

Prahalad Gaggar
  • 10,486
  • 16
  • 46
  • 66
bvr
  • 4,586
  • 17
  • 22
  • 1
    +1: Nice solution. I just had to try it out in [SQLFiddle](http://sqlfiddle.com/#!3/7a5c8/1) – Tony Apr 08 '13 at 14:49
0

From Post The first target is 60, for RebFirst, the second is 1.3 for ReiFirst and 50 for the third, RetFirst.

From Comment I don't need the actual kpiname from this just the value

The following Query will give you the Answer.

select case column_name when 'rebfirst60' then 60
       when 'reifirst1.3' then 1.3
       when 'retfirst50' then 50
from employs
Prahalad Gaggar
  • 10,486
  • 16
  • 46
  • 66
0

I have this now by using the following:

select 
SUBSTRING(SUBSTRING(biographytext,1,10),9,2) as RebookingFirst,
SUBSTRING(SUBSTRING(biographytext,11,20),12,3) as ReInventFirst,
SUBSTRING(SUBSTRING(biographytext,21,30),16,3) as ReCreateFirst,
SUBSTRING(SUBSTRING(biographytext,31,40),20,2) as RetentionFirst,
SUBSTRING(SUBSTRING(biographytext,41,50),23,2) as ReferralsFirst
from employs

This gives me the results for each kpi

Stuart1044
  • 404
  • 5
  • 16