Need some help with inserting text

Question

Need some help with inserting text

mbrady5

SSCrazy

Points: 2835
More actions
June 6, 2019 at 8:30 pm

#3650879
Hi,
I am in a scenario where I need to manipulate some text.
For example:
1. p11034 - If the first letter is 'p' and the count of characters to the right of the 'p' is 5, then insert 4 zeros between the p and 11034.
  1. The end result is 'P000011034'
Any assistance would be great. Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

mbrady5 wrote:

Hi, I am in a scenario where I need to manipulate some text. For example:
p11034 - If the first letter is 'p' and the count of characters to the right of the 'p' is 5, then insert 4 zeros between the p and 11034.
The end result is 'P000011034'
Any assistance would be great. Thanks

would this work?

WITH cteSampleData AS (
  SELECT v.SampleString
  FROM ( VALUES ('P11034'), ('P1103'), ('P110345') ) v(SampleString)
)
SELECT  SampleString
      , NewString = CASE
                      WHEN SampleString LIKE 'p%'
                        AND LEN(SampleString) = 6
                          THEN STUFF(SampleString, 2, 0,'0000')
                      ELSE SampleString
                    END
FROM cteSampleData;

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24386 More actions · Answer 2

This should also work

WITH cteSampleData AS (
  SELECT v.SampleString
  FROM ( VALUES ('P11034'), ('P1103'), ('P110345') ) v(SampleString)
)
SELECT  SampleString
      , NewString = CASE
                      WHEN SampleString LIKE 'p_____'
                          THEN STUFF(SampleString, 2, 0,'0000')
                      ELSE SampleString
                    END
FROM cteSampleData;

How to post data/code on a forum to get the best help.

Lowell SSC Guru Points: 323518 More actions · Answer 3

a pair of examples. if you are only looking for items that are 6 chars and start with P, vs stuffing a bunch of zeros into it no matter what to get it to ten characters in length.

IF OBJECT_ID('tempdb.[dbo].[#SampleData]') IS NOT NULL 
DROP TABLE [dbo].[#SampleData] 
GO
CREATE TABLE [dbo].[#SampleData] ( 
[id]      INT              IDENTITY(1,1)          NOT NULL,
[Colval]  VARCHAR(30)                                 NULL)

INSERT INTO #SampleData VALUES('p5'),('p11034'),('petrol'),('p1433'),('P000011034'),('X14330')

SELECT * ,'P' + '0000' + RIGHT(Colval,5) AS NewVal
FROM #SampleData WHERE LEN(Colval) = 6 AND Colval LIKE 'P%'
--stuff the length with zeros until it is 10 chars, no matter what
SELECT *,
LEFT(Colval,1) + REPLICATE('0',9 - (LEN(Colval) -1) ) + SUBSTRING(Colval,2,LEN(Colval) -1)
 FROM #SampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!