?
Solved

How to check a condition on SELECT statement

Posted on 2011-04-29
13
Medium Priority
?
473 Views
Last Modified: 2012-05-11

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,'-'), field2), field3, .... FROM table

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
0
Comment
Question by:akohan
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35493072

you can use LEN() function

eg
   case
      when LEN(Field1) < 10 then
        Field1 + '0'
      else
        Field1
    end
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35493117
Or, for variable length padding:

select replicate('0',10 -  LEN('123-45')) + '123-45'
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35493146
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 

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 41

Expert Comment

by:Sharath
ID: 35493361
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)
select right('0000000000'+Field1+'-'+Field2,12),Field3 from Table1

Open in new window

declare @Field1 varchar(20),@Field2 varchar(20)
select @Field1 = '2',@Field2 = '1234-5678'
select right('0000000000'+@Field1+'-'+@Field2,12)
-- 02-1234-5678

Open in new window

0
 

Author Comment

by:akohan
ID: 35493521

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

0
 

Author Comment

by:akohan
ID: 35493538

Yes, when I store in SQL Server, I'm asked to store it with hyphen
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35493542
If you are running it on SQL Server and have hyphen in second field, you can try my post.
0
 

Author Comment

by:akohan
ID: 35494350

I'm assuming you mean using select right('0000000000'+Field1+'-'+Field2,12),Field3 from Table1   right?
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
0
 

Author Comment

by:akohan
ID: 35494363

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'  
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35494562
>> if so, then for some reason it doesn't like right()

Are you running it on SQL Server?
0
 

Author Comment

by:akohan
ID: 35494624


no currently I'm using DB2
0
 

Author Comment

by:akohan
ID: 35494738

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.

0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 1000 total points
ID: 35496200
SELECT Right('0000000000', CONCAT(CONCAT(field1,'-'), field2),10), field3, .... FROM table
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question