Strings

[lasd]

Hi

Another question. I have a value in my table employees for eg. sender=15...... so this value is referenced by employees.sender...... for another column in my sql i want to add in 00 before the 15. How do i do this.
i want the last column to have for eg
0015
0031
0051

instead of

15
31
51

thanks for the help
lasd

 

[PHV]

SELECT '00' || sender FROM employees

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LKBrwnDBA]

Which DBMS?

For T-SQL:
Select '00'+sender from employees;
-- or --
Select RIGHT('0000'+sender,4) from employees;

Others:

Select CONCAT('00',sender) from employees;



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[JarlH]

There's no need to ask for dbms since this is the ANSI SQL forum. I suppose lasd is expecting an ANSI compliant solution, just like PHV's answer.

 

[LKBrwnDBA]

Actually the ANSI SQL would be: CONCAT()

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[PHV]

The ANSI SQL function is CONCAT()
The ANSI SQL operator is ||

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

PHV, congrats on winning tipmaster of the week -- again!





r937.com | rudy.ca

 

[JarlH]

Sorry for being such a smartass, but CONCAT is not an ANSI SQL function.

ISO/IEC 9075-2:2003 (E):
"4.2.3 Operations involving character strings
4.2.3.1 Operators that operate on character strings and return character strings
<concatenation operator>is an operator,||,that returns the character string made by joining its character
string operands in the order given."

The word CONCAT isn't mentioned in the standard specification.

 

[r937]

the trouble with the SQL Standard is that you have to spend a couple hunnert bucks if you want to know what's in it



r937.com | rudy.ca

 

[JarlH]

Yeah, I know. Pretty stupid to charge that much if people are supposed to use the standard.