How to search for a string within a string 2

[iwm]

There is a string I need to parse to create several columns. I am using the following statement to create the employeeID column and the Import Date column:

select Substring(rowdata, 1, 9) as 'EmployeeID',
substring (rowdata, 11, 8) as 'Import Date'
from livereqlog

Example of string to be parsed:

rowdata
123456789~20160502~Thomas
[blank]~20160701~Richardson

Example current of output:

This statement doesnt return the desired output when the first 9 characters of the rowdata column are populated with [blank]. If the EmployeeID is blank I would like to return the word 'blank' and if it is populated with a number I would like to return the number.

Example of desired output:

Thank you in advance for your assistance.

Ingrid

 

[fredericofonseca]

instead of using fixed length parsing look at this function and use it instead as your string is a good one for this.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Andrzejek]

You may also check the Length of your rowdata field and determine this way if you have a blank EmployeeID or not.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[DaveInIowa]

If you don't want to write your own Split function (Why isn't Split a native T-SQL function?), you could repurpose the built-in PARSENAME function...

;WITH t AS (
SELECT REPLACE(rowdata, '~', '.') DotData
  FROM livereqlog
)

SELECT PARSENAME(DotData, 3) EmployeeID,
       PARSENAME(DotData, 2) [Import Date]
  FROM t

 

[Olaf Doschke]

In SQL 2016 string_split is a native function:

https://msdn.microsoft.com/en-us/library/mt684588.aspx

Bye, Olaf.

 

[kjv1611]

fredericofonseca, Thanks for sharing. That looks like a very interesting setup.

And thanks to OlafDoschke for mentioning that Split now exists in SQL 2016. Good to know for future reference.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57