Add prefix to results

Question

Add prefix to results

t2 36824

Valued Member

Points: 52
More actions
August 10, 2017 at 4:05 pm

#327932

I have a T-SQL table with 2 columns and want to add a prefix to some of the results. So column 1 is POSOURCE!A!!16 and column 2 is 5. I what to make them POSOURCE!A!!xxx16 and xxx7. My thought is an update statement but not sure of the syntax to use.
Here is what I'm looking at:
select * from params02 where keycode like 'posource!%'
--column 1 is named keycode and column 2 is named dat. Results are POSOURCE!A!!16 in column 1 and 5 in coulmn 2. Row 2 is POSOURCE!A!!17 in column 1 and 7 in column 2. I ran:
update params02 set dat = 'xxx' + dat where keycode like 'posource!%'
to update column 2 but need to know how to do it in coulmn 1 where it's in the middle.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

Honestly, I have no real idea what your requirement is here, apart from it seems you want to change a column with the value POSOURCE!A!!16 to POSOURCE!A!!xxx16. I'm completely guessing that the column will always contain a '!!' before the location you want to put 'xxx', so you could do something like:
CREATE TABLE #YourTable (YourColumn varchar(20)); INSERT INTO #YourTable VALUES ('POSOURCE!A!!16'),('POSOURCE!A!!17'); GO

SELECT * FROM #YourTable;

UPDATE #YourTable SET YourColumn = LEFT(YourColumn,PATINDEX('%!!%', YourColumn) + 1) + 'xxx' + REVERSE(LEFT(REVERSE(YourColumn), PATINDEX('%!!%',REVERSE(YourColumn)) - 1));

SELECT * FROM #YourTable; GO

DROP TABLE #YourTable; GO

I could have guessed that the number on the end is always 2 digits long, which would have made for much simpler SQL, but I didn't want to; in case you do have just single digits or go up to triple (or more).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

t2 36824 Valued Member Points: 52 More actions · Answer 2

So here is what I have and want to do. In the attached picture, the last 2 characters in the Keycode column need a BR in front of them. Same for the Dat column but I just need to run the statement at the top for that one. Looking to do an update statement for that because I have hundreds of rows to do.

When I run this:
UPDATE Params16
SET Keycode = LEFT(Keycode,PATINDEX('%!!%', Keycode) + 1) + 'BR' + REVERSE(LEFT(REVERSE(Keycode), PATINDEX('%!!%',REVERSE(Keycode)) - 1));

I get this:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.

Thom A SSC Guru Points: 99126 More actions · Answer 3

Pictures don't really help. DDL of your table would be great, as I would expect a column of integers to be of the data type intrusion. You won't be able the put a varchar in a into column.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

t2 36824 Valued Member Points: 52 More actions · Answer 4

t2 36824

Valued Member

Points: 52

August 11, 2017 at 6:45 am

#1954956

How's this:

drew.allen SSC Guru Points: 76944 More actions · Answer 5

t2 36824 - Friday, August 11, 2017 6:45 AM
How's this:

That's still a picture, so it still doesn't really help.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

t2 36824 - Friday, August 11, 2017 6:25 AM
When I run this:
UPDATE Params16
SET Keycode = LEFT(Keycode,PATINDEX('%!!%', Keycode) + 1) + 'BR' + REVERSE(LEFT(REVERSE(Keycode), PATINDEX('%!!%',REVERSE(Keycode)) - 1));
I get this:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.

PATINDEX returns 0 if the pattern isn't found in the string. If you subtract 1 from that, you've got an invalid parameter for the LEFT function.

t2 36824 - Friday, August 11, 2017 6:45 AM
How's this:

Looks like another picture to me.

John

Thom A SSC Guru Points: 99126 More actions · Answer 7

John Mitchell-245523 - Friday, August 11, 2017 7:07 AM
t2 36824 - Friday, August 11, 2017 6:45 AM
How's this:

Looks like another picture to me.

John

[/quote]
Have a look at the link in my signature.

Secondly, considering that the Dat field in your image only has 2 different values, '1' and '27'. Why is it a varchar(1000)? Do you have values in there with almost 1,000 digits in there?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

t2 36824 - Friday, August 11, 2017 6:25 AM
So here is what I have and want to do. In the attached picture, the last 2 characters in the Keycode column need a BR in front of them.

What about something like this?
UPDATE Params16
SET Keycode = STUFF( Keycode, LEN(KeyCode) - 1, 0, 'BR')
WHERE LEN(KeyCode) > 2;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

t2 36824 Valued Member Points: 52 More actions · Answer 9

Luis Cazares - Friday, August 11, 2017 7:17 AM
t2 36824 - Friday, August 11, 2017 6:25 AM
So here is what I have and want to do. In the attached picture, the last 2 characters in the Keycode column need a BR in front of them.
What about something like this?
UPDATE Params16
SET Keycode = STUFF( Keycode, LEN(KeyCode) - 1, 0, 'BR')
WHERE LEN(KeyCode) > 2;

Brilliant! That's exactly what I was looking for. Sorry about the bad posting. I'm new to this stuff and still learning.

t2 36824 Valued Member Points: 52 More actions · Answer 10

t2 36824 - Friday, August 11, 2017 8:04 AM
Luis Cazares - Friday, August 11, 2017 7:17 AM
t2 36824 - Friday, August 11, 2017 6:25 AM
So here is what I have and want to do. In the attached picture, the last 2 characters in the Keycode column need a BR in front of them.
What about something like this?
UPDATE Params16
SET Keycode = STUFF( Keycode, LEN(KeyCode) - 1, 0, 'BR')
WHERE LEN(KeyCode) > 2;
Brilliant! That's exactly what I was looking for. Sorry about the bad posting. I'm new to this stuff and still learning.

Any chance you can walk me through exactly what this is saying/doing?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

t2 36824 - Friday, August 11, 2017 8:07 AM
t2 36824 - Friday, August 11, 2017 8:04 AM
Luis Cazares - Friday, August 11, 2017 7:17 AM
t2 36824 - Friday, August 11, 2017 6:25 AM
So here is what I have and want to do. In the attached picture, the last 2 characters in the Keycode column need a BR in front of them.
What about something like this?
UPDATE Params16
SET Keycode = STUFF( Keycode, LEN(KeyCode) - 1, 0, 'BR')
WHERE LEN(KeyCode) > 2;
Brilliant! That's exactly what I was looking for. Sorry about the bad posting. I'm new to this stuff and still learning.
Any chance you can walk me through exactly what this is saying/doing?

Have you looked for the STUFF() documentation?
It's inserting 'BR' into the second to last position and replacing 0 characters with it. Trying to put it in the syntax order:
Insert into the column Keycode, at the second to the last position, replacing zero characters, the string 'BR'.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2