March 19, 2013 at 6:59 pm
Hello, I have some data and need to extract the first 9 numbers from this pattern across multiple rows of data. I would think that using something like this:
substring(value_expression, ,1,9)
might work but am unaware of how to find the first 9 digits in this sequence:
01J416781|1|2013|2|
I am looking to find '01J416781', each row of data starts with this pattern. I would greatly appreciate any help.
March 19, 2013 at 10:14 pm
March 20, 2013 at 7:56 am
That would work if I were looking for '01J416781' however the first 9 characters vary for each row of data so the Select from could result in '01J416781' or '02J517792' or '0TPK03953'. A substring type function is what I'm looking for.
March 20, 2013 at 8:19 am
You really haven't posted much in the way of details other than a somewhat vague description of what you are trying to do.
Maybe you want left(SomeColumn, 9)??
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2013 at 8:34 am
There is only one column of data in the database. Every row in the column is separated by | so | would be comparable to a column with 01J416781 from 01J416781|1|2013|2| being the specific data I am looking to extract.
March 20, 2013 at 8:41 am
I would do something like this if it were my table:
select
ds.Item
from
dbo.YourTable yt
cross apply dbo.DelimtitedSplit8K(yt.YourColumn,'|')ds
where
ds.ItemNumber = 1;
Actually, I would parse the data and put it into a proper table so that I could query it easier.
March 20, 2013 at 8:47 am
CHARINDEX???
create table PseudoTable
(
OneColumnToRuleThemAll varchar(max)
)
insert PseudoTable
select '01J416781|1|2013|2|Value' union all
select '934|f|asdf|qwer|erty|sdfg'
select * from PseudoTable
where CharIndex('|', OneColumnToRuleThemAll) = 10
It would be a lot more helpful if you could take a few minutes and read the first article I reference in my signature for best practices when posting questions. Without details we are just shooting in the dark at what we think you might want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2013 at 12:40 pm
ggarcia 73023 (3/20/2013)
That would work if I were looking for '01J416781'
From your post right from above:
I am looking to find '01J416781', each row of data starts with this pattern.
So?
_____________
Code for TallyGenerator
March 20, 2013 at 1:24 pm
Post the DDL (CREATE TABLE statement) for the table, about 4 or 5 rows of sample data (NOT real data) that mimics the problem domain, and the expected results based on the sample data.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy