Return all characters to the right of a colon in a column

Question

Post reply

Return all characters to the right of a colon in a column

Michael Viglas

SSC-Addicted

Points: 410
More actions
February 19, 2010 at 6:46 am

#138287

I'm sure there's something simple for this, but I have 2 tables one has a column with text, a colon and more text and in my other table, it has everything in the first but only the text after the colon. Is there a command I can use to only return the characters after the colon in the first table? This is the first time I've had to do something like this so any help would be great.
Thanks,
Michael

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Here's one way:

DECLARE @Text VARCHAR(100) = 'abc:xyz';

SELECT

REVERSE(

LEFT(

REVERSE(@Text),

CHARINDEX(':', REVERSE(@Text))-1));

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Michael Viglas SSC-Addicted Points: 410 More actions · Answer 2

I tried that with this command and receive a "Invalid length parameter passed to the SUBSTRING function."

SELECT

REVERSE(LEFT(REVERSE(PART_CODE), CHARINDEX(':', REVERSE(PART_CODE))-1))

FROM PRODUCT_MASTER

Lowell SSC Guru Points: 323518 More actions · Answer 3

some of your part codes do not contain a colon then; add a WHERE statement:

WHERE CHARINDEX(':', PART_CODE) > 0

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!

Paul White SSC Guru Points: 150467 More actions · Answer 4

DECLARE @ProductMaster

TABLE (part_code VARCHAR(50) NOT NULL);

INSERT @ProductMaster VALUES ('S001:Sprocket');

INSERT @ProductMaster VALUES ('W562:Widget');

INSERT @ProductMaster VALUES ('Gearbox');

INSERT @ProductMaster VALUES ('B498:Banana');

INSERT @ProductMaster VALUES ('Q992:Petunia');

INSERT @ProductMaster VALUES ('Chain');

SELECT LEFT(part_code, ISNULL(NULLIF(Colon.position - 1, -1), DATALENGTH(part_code)))

FROM @ProductMaster

CROSS

APPLY (SELECT CHARINDEX(':', part_code COLLATE LATIN1_GENERAL_BIN)) Colon (position);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

GSquared SSC Guru Points: 260824 More actions · Answer 5

Paul, that returns everything left of the colon. He asked for everything right of it.