Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL substring replace function Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 6:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 15, 2013 12:24 PM
Points: 9, Visits: 40
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
Posted Tuesday, March 19, 2013 10:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 4,574, Visits: 8,364
WHERE value_expression LIKE @Pattern + '%'

Is it what you need?
Post #1432982
Posted Wednesday, March 20, 2013 7:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 15, 2013 12:24 PM
Points: 9, Visits: 40
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
Posted Wednesday, March 20, 2013 8:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,093, Visits: 12,574
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1433246
Posted Wednesday, March 20, 2013 8:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 15, 2013 12:24 PM
Points: 9, Visits: 40
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
Posted Wednesday, March 20, 2013 8:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 20,745, Visits: 32,561
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.



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
Posted Wednesday, March 20, 2013 8:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,093, Visits: 12,574
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1433268
Posted Wednesday, March 20, 2013 12:40 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 4,574, Visits: 8,364
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
Posted Wednesday, March 20, 2013 1:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 20,745, Visits: 32,561
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.




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 #1433449
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse