August 21, 2014 at 8:52 pm
hi
How can we extract a column values from database if we know a part of that column value.
For ex: if I have values for a column called statements like this:
(I am good Gal)
(you are a good boy) and so on..
and I want to extract all the statements that have a part called 'good'
what is the best way to query this?
August 22, 2014 at 1:29 am
Quick thought, use the LIKE operator
USE tempdb;
GO
DECLARE @TEST TABLE
(
TEST_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,TEST_STRING VARCHAR(50) NOT NULL
);
INSERT INTO @TEST(TEST_STRING)
VALUES
('I am a bad Gal')
,('I am a good Gal')
,('This is an old horse')
,('This is a good horse')
,('I am a good boy')
,('I am a naugthy boy');
SELECT
*
FROM @TEST T
WHERE T.TEST_STRING LIKE '%good%';
Results
TEST_ID TEST_STRING
----------- ---------------------
2 I am a good Gal
4 This is a good horse
5 I am a good boy
August 22, 2014 at 11:16 am
What to do if we want to replace the word 'good' with 'bad' in all those sentences?
I wrote it like this:
REPLACE(columnname,'good','bad')
Is there any other way to do this?
August 22, 2014 at 11:33 am
Using the REPLACE function is the simplest way to do it. Assuming there aren't any unusual requirements, that's exactly the way I'd do it.
August 22, 2014 at 12:46 pm
Thank you all for the help
August 22, 2014 at 1:07 pm
Note that you might replace strings you don't want them to be replaced.
INSERT INTO @TEST(TEST_STRING)
VALUES
('I am a bad Gal')
,('I am a good Gal')
,('Oh my goodness! There''s no more goodwill')
,('He''s a patient with Osgood–Schlatter disease');
SELECT
*, REPLACE(TEST_STRING, 'good', 'bad')
FROM @TEST T
WHERE T.TEST_STRING LIKE '%good%';
August 22, 2014 at 1:16 pm
Luis Cazares (8/22/2014)
Note that you might replace strings you don't want them to be replaced.
INSERT INTO @TEST(TEST_STRING)
VALUES
('I am a bad Gal')
,('I am a good Gal')
,('Oh my goodness! There''s no more goodwill')
,('He''s a patient with Osgood–Schlatter disease');
SELECT
*, REPLACE(TEST_STRING, 'good', 'bad')
FROM @TEST T
WHERE T.TEST_STRING LIKE '%good%';
DECLARE @STR VARCHAR(50) = 'That an absolute @£$%'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@STR,'e @','ly'),'£',' a '),'$','brilliant'),'%',' point!')
Viewing 7 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