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

Posted by Jayakumars under Sql Server on 6/11/2015 | Points: 10 | Views : 308 | Status : [Member] [MVP] | Replies : 6
Hi
Bandi

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')


Select * from AProd


I need this Output like this

1 Optical Mouse
2 101 Keyboard White
3 Kingston Pendrive Black
4 Monitor
5 MotherBoard

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 6/16/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Bandi on: 6/11/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Jayakumars on: 6/12/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Bandi

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

Mark as Answer if its helpful to you

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

Posted by: Bandi on: 6/12/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Jayakumars on: 6/12/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
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

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

Posted by: Bandi on: 6/12/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Login to post response