|
View previous topic :: View next topic
|
| Author |
Message |
nagarajan.dharani
New User
Joined: 27 Dec 2006
Posts: 36
Location: Chennai
|
|
|
|
Hi,
Can some one help me with the following requirement.
I have a DB2 table and it has a tow columns EMP_NAME and EMP_ID
The EMP_ID is alpha numeric and it is of 7 characters.
The requirement is like i need to create a list of employees where the EMP_ID
has the invalid values. The invalid values are other than A-Z and 0-9.
For example if the EMP_ID is '$34438_' it is invalid because it has a special symbol($)
and a space(_).
Can this be done through a SQL query..??
Thanks,
Dharani |
|
| Back to top |
|
 |
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
SELECT '$34438_' FROM SYSIBM.SYSDUMMY1;
Correct me if I am wrong. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| You are wrong. That will only grab his example and not even from the table he wants to check. |
|
| Back to top |
|
|
vijayakumar.yellala
New User
Joined: 19 Apr 2006
Posts: 63
Location: Chennai
|
|
|
|
I think through SQL query its not possible...
As my suggession is: for that you need to write a code...
first u have to store all the values into table & then you need to check each characker. based on that you can create Invalid emp-ids...
Please correct me if i am wrong.... |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
It isn't all that hard to write but I don't think the performance would be very good. I haven't tested this but it should work.
| Code: |
SELECT EMP_NAME , EMP_ID FROM TABLE
WHERE SUBSTR(EMP_ID,1,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,2,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,3,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,4,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,5,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,6,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,7,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9'); |
|
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
Craq nice brute force solution. Its obvious on how to add lowercase even though the OP is not specifying lower as valid.
I wouldn't want to be there when the DBA comes pounding on the desk saying your query consumed 100% of the CPU for 2 days though. |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1054
Location: Richmond, Virginia
|
|
|
|
You can use BETWEEN with character fields:
IN ('A','B','C','D','E') same as BETWEEN 'A' AND 'E'. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Phrzby Phil wrote: |
You can use BETWEEN with character fields:
IN ('A','B','C','D','E') same as BETWEEN 'A' AND 'E'. |
Yes, it just makes the coding messier. |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1054
Location: Richmond, Virginia
|
|
|
|
You think
| Quote: |
| NOT BETWEEN 'A' AND 'Z' |
is messier than listing 26 values, along with ensuring that they are correct? |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Phrzby Phil wrote: |
You think
| Quote: |
| NOT BETWEEN 'A' AND 'Z' |
is messier than listing 26 values, along with ensuring that they are correct? |
In EBCDIC between 'A' and 'Z' there are 13 characters that are not alphabetic!
There are 6 non-alphabetic characters between I and J, and 7 more between R and S. |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1054
Location: Richmond, Virginia
|
|
|
|
Granted - I tried to slide past that but got caught.
But - if the user "knows" that only display/keyboard characters are used, then the intervening hex values will not be present, and my code works.
In any case, BETWEEN '0' AND '9' works. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
I realize that this will sound horribly "out of tune", but is there some reason the data was not validated before it was put into the database? |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| dick scherrer wrote: |
Hello,
I realize that this will sound horribly "out of tune", but is there some reason the data was not validated before it was put into the database? |
Would you believe it wasn't in the original spec's? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
| Quote: |
| Would you believe it wasn't in the original spec's? |
The longer i do this, the more i'd believe. . .
d |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
