Parsing string

Question

Post reply

Parsing string

Eugene-467296

Grasshopper

Points: 13
More actions
July 20, 2007 at 7:37 am

#173856

Hi Guys,
Could anyone help with a problem. I've got an import table that holds strings, now i need to parse each row discovering whether the string is actually a varchar, date, int or text and then move the data to the relative table for that data type. The problem is to ensure that a date is a date and an int is not a date etc. Does anyone know of a good way of parsing the data to the relevant type, however you dont know what that type is, it has to be determined.

Cheers

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Have you looked at the ISDATE() and ISNUMERIC functions in BOL?

steve smith-401573 SSChampion Points: 10889 More actions · Answer 2

Are there standards / conventions that you can rely upon? Also, what length text are we talking about here? Is there a concatenation of mixed strings, or will the entire field be of a single piece? What's your background level - student, beginner, intermediate, advanced? Give us a little more insight to help answer your question appropriately, ok?

Steve (NOT alias Jones!)

Eugene-467296 Grasshopper Points: 13 More actions · Answer 3

No i had not - cheers. i was trying to figure it out some ridiculous way using cast. That should do the trick although there still seems to be an issue with dates passed in in european format, which they will be.

No concatonation steve, just an import table that holds a reference, a name for the attribute and the text of it, this text column is checked against type and the data is held then in relative tables with the attribute value column of that type, in order to aid searching etc. And i like to think i'm at least intermediate although forgetting about those functions, im not so sure

cheers guys

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

Can you post a sample of your data file to test with?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

This sounds like the text version of a "name/value" table... I'm with MrPoleCat... if you could post a bit of the text table, perhaps we could help.

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

steve smith-401573 SSChampion Points: 10889 More actions · Answer 6

While I agree with Jeff and MrPoleCat (MrPC for short?) that providing a sample would be helpful, let pose this question - do you have any idea how the input source is being built? Is there a solid GUI behind it or is it assumed to be 'dirty' - not necessarily correctly formatted? Also, will this step of the process be 'permitted' or required to either reject erroneous values or clean them up? For instance, if you receive a date in month/year format that requires an interpolation of a true date or storing in 2 fields, not one, yes? (e.g., March 2006 would map in YYMM format as 0602 which, in numeric form, would reduce to 602 - else be interpreted as either the 1st, 15th, 31st or other arbitrary day to provide an unambiguous date).

In short, are there editing rules that we need to know about as well? If so, please enumerate them for the different data types.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Eugene... wanna post some data from the file, please?

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

Eugene-467296 Grasshopper Points: 13 More actions · Answer 8

Cheers for the interest guys, i got around the problem by having a lookup table that checks against attribute for type - which should be set up by the admin anyway and using ISNUMERIC, ISDATE and DATALENGHT in various guises to check the data but i have a far greater problem now if anyone knows a solution id appreciate a pointer.

As stated i have an import table that can be added to, this has a field attribute_value which is of type text as it may take a string in of greater than 8000. But in the sql i cant move the data across to the text table because i cant declare a text as a local variable and i cant pass it to a new stored procedure as the procedure would have to have multiple parameters no matter what way i do it. trying

ie.

exec uspMoveMyData ('param1', param2, (select myTEXTFIELD from myImportTable where id = (int param passed here)))

any ideas how i would do this?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Uh-huh... so... whatcha gonna do with values that could look like this?

SELECT ISNUMERIC('13e05')

SELECT ISNUMERIC('13d05')

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