October 7, 2010 at 12:01 pm
I use to do alot of SQL writing but haven't in a while and i am having trouble doing this insert statement.
I have one table(Table A)
I need columns B and C to add text on the end of the string
Where column A ends in _LG
For example if Column A has a cell "348f6hh5_LG"
Then the query would insert into columns B and C the word " Large" at the end of the existing string.
I know this would be and insert and where combination but i just cant get it too work.
If you have the time to help me with this i appreciate it.
October 7, 2010 at 12:09 pm
Can you please clarify a bit and give sample data with 1 or 2 rows of expected output? You left out a column letter, interchanging LG/Large..we would need some better sample data to give you a correct query the first time.
October 7, 2010 at 12:18 pm
Column A
CUTS833
CUTS833_S
CUTS833_M
CUTS833_L
CUTS833_XL
CUTS833_2X
CUTS833_3X
Column B
S/S Pocket Tee Red Text Royal Fish White
S/S Pocket Tee Red Text Royal Fish White
S/S Pocket Tee Red Text Royal Fish White
S/S Pocket Tee Red Text Royal Fish White
S/S Pocket Tee Red Text Royal Fish White
S/S Pocket Tee Red Text Royal Fish White
Column C
Edge
Edge
Edge
Edge
Edge
Edge
Edge
Basically where column A ends with "_XL"
Columns B and C would add the text "XLarge" on the end of it
and so on with all the sizes, i am trying to figur it our but i am missing something.
Something like
INSERT INTO Table A (Column B, Column C)
Value concatenante "Xlarge" with B & C
SELECT Column A
FROM Table A
WHERE Column A = _XL
October 7, 2010 at 12:21 pm
Are all of your column a values 7 characters, plus _, plus size?
October 7, 2010 at 12:23 pm
No Column A can change length at points in the table
October 7, 2010 at 12:35 pm
Something like this?
DECLARE @data TABLE (
A varchar(50),
B varchar(50),
C varchar(50)
)
INSERT INTO @data
SELECT 'CUTS833','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_S','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_M','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_L','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_XL','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_2X','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_3X','S/S Pocket Tee Red Text Royal Fish White','Edge'
SELECT
A,
B = CASE RIGHT(A,2)
WHEN '_S' THEN B + ' Small'
WHEN '_M' THEN B + ' Medium'
WHEN '_L' THEN B + ' Large'
WHEN 'XL' THEN B + ' XL'
WHEN '2X' THEN B + ' 2X'
WHEN '3X' THEN B + ' 3X'
ELSE B
END,
C = CASE RIGHT(A,2)
WHEN '_S' THEN C + ' Small'
WHEN '_M' THEN C + ' Medium'
WHEN '_L' THEN C + ' Large'
WHEN 'XL' THEN C + ' XL'
WHEN '2X' THEN C + ' 2X'
WHEN '3X' THEN C + ' 3X'
ELSE C
END
FROM @data
October 7, 2010 at 12:46 pm
You're trying to insert new rows when you should be updating existing rows.
UPDATE A
SET B = B + ' Large', C = C + ' Large'
WHERE A LIKE '%LG'
I assume that you want a space between the original text and the Large.
You can also use a CASE statement to vary the text appended if you want to update all of the records at the same time instead of writing separate queries for each pattern for A.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 7, 2010 at 12:59 pm
Mine modified to Update instead:
DECLARE @data TABLE (
A varchar(50),
B varchar(50),
C varchar(50)
)
INSERT INTO @data
SELECT 'CUTS833','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_S','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_M','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_L','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_XL','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_2X','S/S Pocket Tee Red Text Royal Fish White','Edge' UNION ALL
SELECT 'CUTS833_3X','S/S Pocket Tee Red Text Royal Fish White','Edge'
UPDATE @data
SET B = CASE RIGHT(A,2)
WHEN '_S' THEN B + ' Small'
WHEN '_M' THEN B + ' Medium'
WHEN '_L' THEN B + ' Large'
WHEN 'XL' THEN B + ' XL'
WHEN '2X' THEN B + ' 2X'
WHEN '3X' THEN B + ' 3X'
ELSE B
END, C = CASE RIGHT(A,2)
WHEN '_S' THEN C + ' Small'
WHEN '_M' THEN C + ' Medium'
WHEN '_L' THEN C + ' Large'
WHEN 'XL' THEN C + ' XL'
WHEN '2X' THEN C + ' 2X'
WHEN '3X' THEN C + ' 3X'
ELSE C
END
SELECT * FROM @data
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy