Split a Record using Substring

Question

Split a Record using Substring

mk.kamalanand

Grasshopper

Points: 18
More actions
December 22, 2017 at 1:53 am

#402778

Hi All,
I have records stored in a table in below formats . Want the result in an arranged format as shown below. I tried with substring but not get it as required. Can anyone help to get it done.
Description
------------------------------------------------------------------------------------------------------------------------------------------------
String '33417' does not match regex pattern '^[0-9]{5}([ -]?)[0-9]{4}$'. Path 'Draft1[38]['Emp Code']', line 638, position 125.
Invalid type. Expected String, Numbers have Null. Path 'Draft1[1].DOB', line 131, position 11.
String ' ' does not validate against format 'date-time'. Path 'Draft1[40]['Hire Date']', line 47, position 5.
Invalid type. Expected String, Number Null. Path 'Draft1[124].DOB', line 1004, position 179.
String '69316' does not match regex pattern '^[0-9]{4}([ -]?)[0-9]{4}$'. Path 'Draft1[13]['Emp Name']', line 1364, position 215.
Result should be like
Description
------------------------------------------------------------------------------------------------------------------------------------------------
In Document 'Draft1 - Column['Emp Code'] - Row Number[38]'. --> String '33417' does not match regex pattern '^[0-9]{5}([ -]?)[0-9]{4}$'.
In Document 'Draft1 - Column.DOB - Row Number[1]'. --> Invalid type. Expected String, Numbers have Null.
In Document 'Draft1 - Column['Hire Date'] - Row Number[40]'. --> String ' ' does not validate against format 'date-time'.
In Document 'Draft1 - Column.DOB - Row Number[124]'. --> Invalid type. Expected String, Number Null.
In Document 'Draft1 - Column['Emp Name'] - Row Number[13]'. --> String '69316' does not match regex pattern '^[0-9]{4}([ -]?)[0-9]{4}$'.
Thanks,
Anand

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

I'm honestly not sure SQL Server is the correct answer to doing this. This is going to take A LOT more than a SUBSTRING. Especially if that error is stored in a single column.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

mk.kamalanand - Friday, December 22, 2017 1:53 AM
Hi All,
I have records stored in a table in below formats . Want the result in an arranged format as shown below. I tried with substring but not get it as required. Can anyone help to get it done.
Description
------------------------------------------------------------------------------------------------------------------------------------------------
String '33417' does not match regex pattern '^[0-9]{5}([ -]?)[0-9]{4}$'. Path 'Draft1[38]['Emp Code']', line 638, position 125.
Invalid type. Expected String, Numbers have Null. Path 'Draft1[1].DOB', line 131, position 11.
String ' ' does not validate against format 'date-time'. Path 'Draft1[40]['Hire Date']', line 47, position 5.
Invalid type. Expected String, Number Null. Path 'Draft1[124].DOB', line 1004, position 179.
String '69316' does not match regex pattern '^[0-9]{4}([ -]?)[0-9]{4}$'. Path 'Draft1[13]['Emp Name']', line 1364, position 215.
Result should be like
Description
------------------------------------------------------------------------------------------------------------------------------------------------
In Document 'Draft1 - Column['Emp Code'] - Row Number[38]'. --> String '33417' does not match regex pattern '^[0-9]{5}([ -]?)[0-9]{4}$'.
In Document 'Draft1 - Column.DOB - Row Number[1]'. --> Invalid type. Expected String, Numbers have Null.
In Document 'Draft1 - Column['Hire Date'] - Row Number[40]'. --> String ' ' does not validate against format 'date-time'.
In Document 'Draft1 - Column.DOB - Row Number[124]'. --> Invalid type. Expected String, Number Null.
In Document 'Draft1 - Column['Emp Name'] - Row Number[13]'. --> String '69316' does not match regex pattern '^[0-9]{4}([ -]?)[0-9]{4}$'.
Thanks,
Anand

Help us help you. See the first link under "Helpful Links" in my signature for how to post readily consumable data and post your code so we can try to figure out what you're really trying to do. It looks like you may have a pretty decent data validation system/bulk log staging table setup but it's difficult to advise you how to format your "validation failed" messages without you posting your code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)