INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to search for a string within a string

How to search for a string within a string

(OP)
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

RE: How to search for a string within a string

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.

RE: How to search for a string within a string

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...

CODE

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

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

RE: How to search for a string within a string

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close