• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

SUBSTRING

Hi Everybody,

I'm facing problem in writing a sql query that will select a sub-string in a string. There is a column in table called description and its data type is nvarchar(50). It holds values like

XXX,C700,3/4,XXXXXXXX,1 CU.FT.
XXX,C700,1 1/2,XXXXXXXX,10 CU.FT.

and I need to select the size part of the string, for eg 3/4 or 1 1/2. I see pattern in the values, the size part always comes after the second , (comma) in the string. Can you please suggest me a sql that will select only that part.

Thanks
0
Samoin
Asked:
Samoin
  • 4
  • 3
  • 2
1 Solution
 
BrandonGalderisiCommented:
declare @DAta nvarchar(50)
set @data=N'XXX,C700,1 1/2,XXXXXXXX,10 CU.FT.'

select substring(substring(@data, charindex(',', @data,charindex(',', @data)+1)+1,100), 1, charindex(',',substring(@data, charindex(',', @data,charindex(',', @data)+1)+1,100))-1)
0
 
SamoinAuthor Commented:
Well the data in the column is not fixed, sometimes the value is like

ABB,PSMT,5/8x3/4,PULSER,10 GAL.
ABB,5UM20,3/4,PULSER,GAL,HOT WATER,1 GAL.
Neptune,T-10,1 1/2,PULSEGEN,6DIGIT,1000 GAL.
Neptune T-10 3/4 ARB-V 4 Digit 1000 Gal

So initializing a particular value won't help I guess.
0
 
udaya kumar laligondlaTechnical LeadCommented:
I am assuming that the first two fields are of 3 and 4 chars and the second comma is always the 9th
declare @Test nvarchar(500)
set @Test=N'XXX,C700,1 1/2,XXXXXXXX,10 CU.FT.'

select substring(@Test, charindex(',', @Test,9)+1,charindex(',',@Test,charindex(',', @Test,9)+1)-charindex(',', @Test,9)-1)
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
udaya kumar laligondlaTechnical LeadCommented:
use
select substring(substring(description, charindex(',', description,charindex(',', description)+1)+1,100), 1, charindex(',',substring(description, charindex(',', description,charindex(',', description)+1)+1,100))-1) from tablename
0
 
BrandonGalderisiCommented:
My example was just a sample.  Let's say your field is called SomeField and your table is called SomeTable.

Your query would then be:

And uday, I would appreciate you at least say "i'm copying Brandon's code" when you post an update ;).
select substring(substring(SomeField, charindex(',', SomeField,charindex(',', SomeField)+1)+1,100), 1, charindex(',',substring(SomeField, charindex(',', SomeField,charindex(',', SomeField)+1)+1,100))-1)
from SomeTable

Open in new window

0
 
SamoinAuthor Commented:
Uday and Brandon,

I keep getting this error

Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
0
 
SamoinAuthor Commented:
Got it,

I did a small change in the query

select substring(substring([description], charindex(',', [description],charindex(',', [description])+1)+1,100), 1, charindex(',',substring([description], charindex(',', [description],charindex(',', [description])+1)+1,100)))
from [TableName]
0
 
BrandonGalderisiCommented:
uday copied my script and just changed the field names from my "sample @Data".  My script showed you what to do.  I think that I have earned this.
0
 
SamoinAuthor Commented:
Fair Enough, and I think I have assigned points according to the effort but again I will say it was a combined effort and major contribution was yours.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now