FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


SQL script: String function how to?





vickriz
Can anybody help me how to get the first character from a field string and then concatenate it with id incremental value as new IDNumber.

Lets say we have branch office namely Manila, Davao, Zamboanga and we have uniqueID as incremental. Then how can i make a sql script (MySQL/ACCESS) to combine and first character of branch name + the uniqueID.

Records of IDnumber should be something like this:

M000001
M000002
M000003
D000001
D000003
Z000001
...so fort
hexkid
Like this?
Code:
mysql> select * from xoff;
+----+-----------+-------+
| id | office    | extra |
+----+-----------+-------+
|  1 | Manila    | one   |
|  2 | Manila    | two   |
|  3 | Davao     | three |
|  4 | Manila    | four  |
|  5 | Zamboanga | five  |
|  6 | Manila    | six   |
+----+-----------+-------+
6 rows in set (0.00 sec)

mysql> select concat(substr(office, 1, 1), substr(concat('00000', id), -6)) as new, extra from xoff;
+---------+-------+
| new     | extra |
+---------+-------+
| M000001 | one   |
| M000002 | two   |
| D000003 | three |
| M000004 | four  |
| Z000005 | five  |
| M000006 | six   |
+---------+-------+
6 rows in set (0.00 sec)

Probably you can do something similar in ACCESS.
vickriz
hexkid wrote:

mysql> select concat(substr(office, 1, 1), substr(concat('00000', id), -6)) as new, extra from xoff;
..
....
Probably you can do something similar in ACCESS.


From that query in section "substr(concat('00000', id), -6)) ..." that only work in a single digit right? But my records of ID is far up to 005334. How do I gonna implement these query? I've tried this in Access but I got error.

QUERY
Code:
SELECT CONCAT(SUBSTR(branchName, 1, 1), SUBSTR(CONCAT(uniqueID, id), -6)) AS idNumber, extra FROM xoff;


ERROR


CONCAT function does not work in MSAccess thats why I used REPLACE.
hexkid
vickriz wrote:
From that query in section "substr(concat('00000', id), -6)) ..." that only work in a single digit right?

No.

concat('00000', id) will generate a string starting with 5 zeroes and followed by the id, for example "000007" or "0000033587". Then, substr(XXX, -6) returns the last 6 characters from the string.
So substr('0000033587', -6) returns "033587".


Sorry, can't help you with ACCESS functions.
vickriz
Thank you so much bro, I really appreciate your help.

But then I'd like to try again to look for someone that could might help me to solve my query.
vickriz
I have found my own solution to my problem.

Thanks again to someone who help me, it inspire me to do my own..

Here's the script SQL:
Code:
UPDATE xOffice SET idNumber = LEFT(branchName,1)+'00000'+RIGHT(STR(id),1);
Laughing Laughing
hexkid
vickriz wrote:
Code:
UPDATE xOffice SET idNumber = LEFT(branchName,1)+'00000'+RIGHT(STR(id),1);
Again, I can't test Access functions, but don't you mean?
Code:
UPDATE xOffice SET idNumber = LEFT(branchName,1)+RIGHT('00000'+STR(id),6);
vickriz
hexkid wrote:
Again, I can't test Access functions, but don't you mean?
Code:
UPDATE xOffice SET idNumber = LEFT(branchName,1)+RIGHT('00000'+STR(id),6);


Actually that's the one I used before. I got a result but it has contain a white space inbetween the 0000 and the incremental value.



The result after I revise:
hexkid
vickriz wrote:
hexkid wrote:
Again, I can't test Access functions, but don't you mean?
Code:
UPDATE xOffice SET idNumber = LEFT(branchName,1)+RIGHT('00000'+STR(id),6);


Actually that's the one I used before. I got a result but it has contain a white space inbetween the 0000 and the incremental value.

I suppose that space is for the sign (which only appears if the number is negative). But you'll have problems when the id is 10 or more!

Again, I do not know Access.
There probably is a better function to convert a number to string and ignore the space for the sign, but you (probably) can do it like this
Code:
RIGHT('00000'+RIGHT(STR(id), LEN(STR(id))-1), 6)
kv
If it is a one time job, exporting data to excel, making modifications and importing back is the easiest way.

If you have to do it repeatedly, you could write vb code to traverse through the records, take the first character, append it with value of a counter and update the current record. This way you could do things more complicated than this and need not break your head on sql. On the slip side, it is slower than sql and works only in access.
vickriz
Good day!


Code:
UPDATE xOffice SET idNumber = LEFT(branchName,1)+RIGHT('00000'+RIGHT(STR(id), LEN(STR(id))-1), 6)

I used this query and it yes work like a charm!

You're really magnificent, thanks for your help. And also thanks to kv for sharing opinion (the process of my query is on the back-end hehehe... Laughing

Mabuhay!! (LongLive!!)
Related topics
I can't upload my Database SQL script ! Helllppppp!!!!
An Example to mySQL class
Database connection class!
Array Update SQL script not working
Site Nav Suggestions...
A ActionScript,showing a running date.
Looking for easy FormMail Script
importing SQL databases
Database Privilege
automatic use of .sql file
SQL UPDATING TABLE
Javascript replace by array
JSP Help!!!
When clicked goes into input field??
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.