T-SQL substring replace function

Question

Post reply

T-SQL substring replace function

ggarcia 73023

SSC Veteran

Points: 285
More actions
March 19, 2013 at 6:59 pm

#272367

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

WHERE value_expression LIKE @Pattern + '%'

Is it what you need?

_____________
Code for TallyGenerator

ggarcia 73023 SSC Veteran Points: 285 More actions · Answer 2

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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/

ggarcia 73023 SSC Veteran Points: 285 More actions · Answer 4

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

CHARINDEX???

create table PseudoTable

(

OneColumnToRuleThemAll varchar(max)

)

insert PseudoTable

select '01J416781|1|2013|2|Value' union all

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/

Sergiy SSC Guru Points: 110208 More actions · Answer 7

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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.