String function

Question

  

I want only certain string from following string. i need to extract only X111,A007&S112 from below example. 

 its not with fix length but it has | before starts. Is there any function to get value.

Example:

            column name 111 

  'it is a number with code|x111 - Ferrari'
   'Aviation and air force|A007 - Air service '
   'Electronics and Hardware and software|S112 - parts of computer' 

Looking for:

            column name 111 

     x111

     A007

     S112

Answer 1

Good day Steve,

Please check if the following solve your needs:

DECLARE @t VARCHAR(1000) = 'Electronics and Hardware and software|S112 - parts of computer' 
SELECT SUBSTRING(
		@t,
		CHARINDEX ('|', @t)+1,
		CHARINDEX (' ', @t, CHARINDEX ('|', @t)) - CHARINDEX ('|', @t) - 1
	)

You can use the same logic with data in table

SELECT SUBSTRING(
		Column_Name,
		CHARINDEX ('|', Column_Name)+1,
		CHARINDEX (' ', Column_Name, CHARINDEX ('|', Column_Name)) - CHARINDEX ('|', Column_Name) - 1
	)
FROM Table_Name

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 2

Helo Steve,

You can also use below script: (it won't throw error for column value ''-empty)

DECLARE @TAB AS TABLE(INPUT VARCHAR(500));
INSERT @TAB VALUES 
(''),
('it is a number with code|x111 - Ferrari'),
('Aviation and air force|A007 - Air service '),
('Electronics and Hardware and software|S112 - parts of computer');

SELECT 	INPUT,
SUBSTRING(
   (SUBSTRING(
 			INPUT,
			CHARINDEX ('|', INPUT)+1,
			LEN(INPUT) 
		  )
	),
	0,
	CHARINDEX
	(' ',(SUBSTRING(
		INPUT,
		CHARINDEX ('|', INPUT)+1,
		LEN(INPUT)
	)),0)
	) AS EXPECTED_RESULTSET
FROM @TAB;

Please mark this as answer if it solved your issue. 
Please vote this as helpful if it helps to solve your issue. 

Thanks in Advance,
Arulmouzhi

Answer 3

select parsename( replace(replace([column name 111],'|','.'),' -','.'), 2)

from yutable

Answer 4

Hi Ronen,

The code throws error if the input is blank/empty.

Answer 5

Hi Jingyang Li,

The code changes the input as null if the input is blank/empty. 

Answer 6

Nice catch Arulmouzhi
+5

Thanks

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]