How to Achieve this substring Concept here - Bandi [Resolved]

by using SubString function,

SELECT SUBSTRING( ProdName, ISNULL(NULLIF(CHARINDEX('Product of', ProdName),0)+LEN('Product of')+1 , 1), LEN(ProdName))
FROM AProd

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Select SUBSTRING( ProdName, LEN('Product of ')+2, LEN(ProdName))
,SUBSTRING( ProdName, 12, LEN(ProdName))  from AProd 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Hi
Bandi

I no need mention string length this 12 how to mention automatically string length?

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Select SUBSTRING( ProdName, LEN('Product of')+2 , LEN(ProdName)) 
FROM AProd

That was the reason, I have given you the two columns ( 1. calculating length of 'Product of' string by LEN() function; 2. with hardcoded length value )

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Hi
bandi

I have to need some changes this please can you solve this

Create Table AProd
(
ProdId int primary key identity(1,1),
ProdName varchar(300)
)

Insert into AProd values('Product Of Optical Mouse')
Insert into AProd values('Product of 101 Keyboard White')
Insert into AProd values('Product of Kingston Pendrive Black')
Insert into AProd values('Product of Monitor')
Insert into AProd values('Product of MotherBoard')
Insert into AProd values('Product of Optical Mouse MotherBoard1 MotherBoard2 Kingston Pendrive Black-9500')
Insert into AProd values('Microsoft Word')
Insert into AProd values('Excel')

-- I need output this

1 Optical Mouse
2 101 Keyboard White
3 Kingston Pendrive Black
4 Monitor
5 MotherBoard
6 Optical Mouse MotherBoard1 MotherBoard2 Kingston Pendrive Black-9500
7 Microsoft Word
8 Excel

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Select REPLACE( prodName, 'Product of ', '') as ProdName FROM AProd

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator