Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

extracting column values when a part of the column value is known Expand / Collapse
Author
Message
Posted Thursday, August 21, 2014 8:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 13, 2014 4:48 PM
Points: 10, Visits: 14
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?
Post #1606094
Posted Friday, August 22, 2014 1:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 2,018, Visits: 5,502
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
Post #1606133
Posted Friday, August 22, 2014 11:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 13, 2014 4:48 PM
Points: 10, Visits: 14
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?
Post #1606433
Posted Friday, August 22, 2014 11:33 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 4,062, Visits: 3,498
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1606444
Posted Friday, August 22, 2014 12:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 13, 2014 4:48 PM
Points: 10, Visits: 14
Thank you all for the help
Post #1606468
Posted Friday, August 22, 2014 1:07 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 3,644, Visits: 8,158
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%';




Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1606478
Posted Friday, August 22, 2014 1:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 2,018, Visits: 5,502
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!')

Post #1606480
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse