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

Extracting values from a field value in a table

I have the following table

(field1,field2, field 3, field4, field5) and assuming filed1 as a primary key and the values for field2 has a special property that for each row filed2=field3filed4field5. For example for a given row if we have the value of fiedld2 as A04UV then it means that the value for filed3 is A , the value for field4 is 04 and the value for field5 is UV . The values for field5 and field3 are always non numeric and can have a length of either 1 or 2 or they can be NULL. However, the value of field 4 has the following property
 =>The length of the value for field4 can always be 2. In other words field4 values are of the form 0X, where x any number between 1 to 9 or of the form xy where both x and y are between 1 and 9.

So the question is :

The table is created from a datasource which only had values for field1, field2 and I want to create another table for which the values of filed3,field4 and field5 are extracted from the value of field2 for each row in table . The values for filed1 and field2 can be the same in the new table what I want is to extract the values for filed3,filed4 and field5  from field2 based on their relationships described above. I dont want a c# or vb.net code what I want is to do this in SQL
0
AbebeDemeke
Asked:
AbebeDemeke
  • 5
  • 4
  • 3
1 Solution
 
autosblindoCommented:
I'd like to know if the following values are all allowed:

- AA99ZZ
- AA00ZZ
- A99Z
- A00Z
- AA99
- AA99Z
- AA00
- AA00Z
- A99ZZ
- A99Z
- A00ZZ
- A00Z
- 00ZZ
- 00Z
- 99ZZ
- 99ZZ
- 00
- 99

Not allowed:

- 1A1Z
- AAZZ
- A1Z

Thank you
0
 
AbebeDemekeAuthor Commented:
YES YOU ARE RIGHT THE FIRST GROUP IS ALLOWED AND THE SECOD ARE NOT
0
 
autosblindoCommented:
ok, give me few minutes, i'll write the query.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
AbebeDemekeAuthor Commented:
thanks man, you are given the minutes. ha ha ha
0
 
tigin44Commented:
you can populate your new table with the following
INSERT INTO yourNewTable 
SELECT field1, field2, 
	   NULLIF(SUBSTRING(field2, 1, PATINDEX('%[0-9]%', field2) - 1), '') as field3,
	   CAST(SUBSTRING(field2, PATINDEX('%[0-9]%', field2), 2) AS int) as field4,
	   NULLIF(SUBSTRING(field2, PATINDEX('%[0-9]%', field2) + 2, LEN(field2) - PATINDEX('%[0-9]%', field2) + 2), '') as field5
FROM yourTable

Open in new window

0
 
autosblindoCommented:
The solution posted by tingin44 is 100 times better and efficient than the one i had in my mind.
I tried it and it worked fine.

There is no reason I continue with my idea.

Thank you.
0
 
autosblindoCommented:
I did it, just for fun (and because i'm studing SQL). I say again, tigin44 solution is the best, (accept only his solution, please).

SELECT field1, field2,
      (CASE
            WHEN left(field2,2) LIKE '[A-Z][A-Z]' THEN left(field2,2)
            WHEN left(field2,1) LIKE '[A-Z]' THEN left(field2,1)
            ELSE null
      END) AS filed3,
      (CASE
            WHEN left(field2,2) LIKE '[A-Z][A-Z]' THEN substring(field2,3,2)
            WHEN left(field2,1) LIKE '[A-Z]' THEN substring(field2,2,2)
            ELSE left(field2,2)
      END) AS filed4,
      (CASE
            WHEN right(field2,2) LIKE '[A-Z][A-Z]' THEN right(field2,2)
            WHEN right(field2,1) LIKE '[A-Z]' THEN right(field2,1)
            ELSE null
      END) AS field5
FROM Table

Bye.
0
 
AbebeDemekeAuthor Commented:
THIS IS an excellent solution man. BUt I have another problem that is just similar to this one but the requirement is a little bit different. That is , in any row either field3 or filed5 or both should be null. In other words, if filed3 is non NULL then filed5 should be and vice vesa. Or both should be null
0
 
AbebeDemekeAuthor Commented:
by the way I only tested tingin44' s solution
0
 
tigin44Commented:
my solution handles the field3 and for for the null values...
0
 
tigin44Commented:
my solution handles the field3 and field5 for for the null values... either 1 or both can be null my code traps them and gives correct result
0
 
AbebeDemekeAuthor Commented:
Thanks any ways.Great job!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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