T-SQL substring replace function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » T-SQL substring replace function

T-SQL substring replace function

Rate Topic

Display Mode

Topic Options

Author

Message

ggarcia 73023

Posted Tuesday, March 19, 2013 6:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 1:27 PM
Points: 9, Visits: 38

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.

Post #1432967

Sergiy

Posted Tuesday, March 19, 2013 10:14 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 6:59 PM
Points: 4,557, Visits: 8,215

WHERE value_expression LIKE @Pattern + '%'

Is it what you need?

Post #1432982

ggarcia 73023

Posted Wednesday, March 20, 2013 7:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 1:27 PM
Points: 9, Visits: 38

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.

Post #1433225

Sean Lange

Posted Wednesday, March 20, 2013 8:19 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 1:17 PM
Points: 8,641, Visits: 8,273

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1433246

ggarcia 73023

Posted Wednesday, March 20, 2013 8:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 1:27 PM
Points: 9, Visits: 38

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.

Post #1433254

Lynn Pettis

Posted Wednesday, March 20, 2013 8:40 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 21,635, Visits: 27,495

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.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1433260

Sean Lange

Posted Wednesday, March 20, 2013 8:47 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 1:17 PM
Points: 8,641, Visits: 8,273

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1433268

Sergiy

Posted Wednesday, March 20, 2013 12:40 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 6:59 PM
Points: 4,557, Visits: 8,215

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?

Post #1433416

Lynn Pettis

Posted Wednesday, March 20, 2013 1:24 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 21,635, Visits: 27,495

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1433449

« Prev Topic | Next Topic »

Permissions