akohan
asked on
How to check a condition on SELECT statement
Hello,
In an SQL query statement which I'm sending to a remote machine I have:
SELECT field1, field2, field3, .... FROM table and ....
However, database designer explained that field1 and field2 together are part number (together) so I used CONCAT() function to deal with that piece of information as a whole meaning that:
SELECT CONCAT(CONCAT(field1,'-'),
So if the data is 2 and 1234-5678 becomes 2-1234-5678
Or if 18 and 1234-5678 becomes 18-1234-5678
Now, I need to add '0' to field if it is less than 10 and show it and store it in a 2nd database as 02-1234-5678. Which function can I use in an SQL statement to check the length of field1 and add a zero on the fly?
Regards,
ak
Or, for variable length padding:
select replicate('0',10 - LEN('123-45')) + '123-45'
select replicate('0',10 - LEN('123-45')) + '123-45'
For clarity, with a variable, this is basically an LPAD() function:
declare @num varchar(10)
set @num = '123-45'
select replicate('0', 10-LEN(@num)) + @num 
Q1: The question is both in MySQL and SQL Server zones. What is your database?
Q2: Do you always have hyphen in the second field? If yes, you can try this. (This is SQL Server version)
Q2: Do you always have hyphen in the second field? If yes, you can try this. (This is SQL Server version)
select right('0000000000'+Field1+'-'+Field2,12),Field3 from Table1declare @Field1 varchar(20),@Field2 varchar(20)
select @Field1 = '2',@Field2 = '1234-5678'
select right('0000000000'+@Field1+'-'+@Field2,12)
-- 02-1234-5678ASKER
Hi Sharath,
Good question! I'm retrieving those data from a mainframe and storing it in SQL Server! so it must be something that AS/400 can understand. I'm not AS/400 expert and not sure if it support stored proc.
Regards,
ak
ASKER
Yes, when I store in SQL Server, I'm asked to store it with hyphen
If you are running it on SQL Server and have hyphen in second field, you can try my post.
ASKER
I'm assuming you mean using select right('0000000000'+Field1+
if so, then for some reason it doesn't like right()
also, I'm not sure why you are using 10 0s in there.
Regards,
ak
ASKER
Hi mrjoltcola,
can you let me know why you are using replicate()?
how this can add a zero in the beginning of result?
select replicate('0',10 - LEN('123-45')) + '123-45'
>> if so, then for some reason it doesn't like right()
Are you running it on SQL Server?
Are you running it on SQL Server?
ASKER
no currently I'm using DB2
ASKER
Now works changing the 2nd argument in RIGHT() function. However, I'm not sure if is reliable.
see the data I'm getting has a length of 13 characters so it should be RIGHT(field, 13) but it is not doing it right however, 16 makes it work but not sure if this is OK.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can use LEN() function
eg
case
when LEN(Field1) < 10 then
Field1 + '0'
else
Field1
end