Get column data from string

[sparkbyte]

This is close but need a bit of help.

[Select 
	substring(String1,PATINDEX('%[a-zA-z][a-zA-z][0-9][0-9][0-9][0-9]%',String1),6)
	as FileLocation 
from [AFiles]

ABC - ALL BINARY CHARACTERS A32132132 AA - FILE ROOM NUMBER 1 LL A 1022 - ROW AA SHELF 1022 - [HIGHLIGHT]AA1022[/HIGHLIGHT] ABCBCA
Consolidated: SRC32132132

The problem with this is that is is also finding the SRC32132132 as a match. The only field that it should find is the highlighted 6 digit field.



Thanks

John Fuhrman

 

[markros]

You may want to search for space preceding 2 characters.

PluralSight Learning Library

 

[sparkbyte]

How would I tell it to search for the space??

There will be a space before and after the needed text.

I think this is working but still testing.

Select 
	Left(String1,3) As FRC

	,Case 
		When PATINDEX('%[A][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',String1) <> 0 Then
			SUBSTRING(String1,PATINDEX('%[A][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',String1),10)
	End As Afile

	,Case
		When PATINDEX('%[a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9]%',String1) <> 0 Then
		SUBSTRING(String1,PATINDEX('%[a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9]%',String1),6)
	End As Afile_Location
from [AFiles] 
Where PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9] %',String1) <> 0

Thanks

John Fuhrman

 

[markros]

Simple patindex('% [A-z][A-z][0-9]etc. %',..) should work, in other words, add a space before and after the pattern if you know it always has spaces before and after.

PluralSight Learning Library