Padding a record in a field for a stored procedure SQL Server

I have a field that will either be a record of 6 characters or 3 From a merged database and older system to a newer set of records. The records should have a "4" in front of the characters and two "00" in back. the Field is called {prod_cat} for Product Catagory.

130
131
132
133
321

I have to do this in a SQL Server 2008 Stored Procedure.
Can someone tell me how to do this.....?
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Copy-paste the below code into your SSMS, execute to verify it works, and modify to meet your needs.
CREATE TABLE #tmp (no int)

INSERT INTO #tmp (no)
VALUES (130), (131), (132), (133), (321), (7), (1234)

SELECT RIGHT('00004' + CAST(no as varchar(5)),5)  + '00' as result
FROM #tmp

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
correction, modified to pass the six-digit number if it's in your set, else the logic spelled out.
CREATE TABLE #tmp (no int)

INSERT INTO #tmp (no)
VALUES (130), (131), (132), (133), (321), (123456), (654321)

SELECT CASE 
	WHEN LEN(no) = 6 THEN no
	ELSE RIGHT('00004' + CAST(no as varchar(5)),5)  + '00' END as result
FROM #tmp

Open in new window

0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I think I rephrased that incorrectly. The values are in a field those are examples of the records. Sorry. I thought it would be more like a check

if the record length is = 6 then ignore the change
If the record length is =3 then apply the padding
if the record is null or blank then ignore the entire record
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>if the record is null or blank then ignore the entire record
alrightythen... added the WHERE clause
CREATE TABLE #tmp (no int)

INSERT INTO #tmp (no)
VALUES (130), (131), (''), (132), (133), (NULL), (321), (123456), (654321)

SELECT CASE 
	WHEN LEN(no) = 6 THEN no
	ELSE RIGHT('00004' + CAST(no as varchar(5)),5)  + '00' END as result
FROM #tmp 
WHERE COALESCE(no, '') <> ''

Open in new window

0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
No it is to put the "4" in front of the field... like this.
130
131
132
133
321

413000
413100
413200
413300
432100
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That's what my last comment does.   I used the RIGHT() as it wasn't clear if there would always be three or six characters, or maybe four or five.  If it's always three, then we can get rid of the RIGHT in the ELSE block...
CREATE TABLE #tmp (no int)

INSERT INTO #tmp (no)
VALUES (130), (131), (''), (132), (133), (NULL), (321), (123456), (654321)

SELECT CASE 
	WHEN LEN(no) = 6 THEN no
	ELSE '4' + CAST(no as varchar(3))  + '00' END as result
FROM #tmp 
WHERE COALESCE(no, '') <> '' 

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So ... done deal?
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I am not sure I could use it because I do not know all the values as the field has 1000's of these codes that need to be padded. The way it was phrased...I assume I would have to go through all the codes and manually put them in I spoke of the field {prod_cat} and whether or not it would be used in the code....? Sorry new to coding ask mlmcc. He will tell you I am green as an Irish Meadow.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If you're talking about the character length of the code, yeah I've had to deal with that before.  As an example, the below code will guarantee a zero-left-padding of up to 10 characters, regardles of the length of @no.
SELECT RIGHT('0000000000' + CAST(@no as varchar(10)),10) 

Open in new window


If you're talking about different codes have different logic for how you want to display them, for example anything beginning with a 9 needs a prefix of 'ABC', then that'll be the CASE block with multiple WHEN..THEN lines.
SELECT CASE 
    WHEN @no > 10000 then 'something' 
    WHEN CAST(@no as varchar(10)) = '90' THEN 'ABC' + CAST(@no as varchar(10))
    WHEN @pigs_fly = 1 THEN 'banana' END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
ruavol2 - Thanks for all the grades.  Good luck.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.