![]() , col_01 = ltrim(substring(v.inputString, 1, p01.pos - 2)) Here is a function that can be used to return up to 12 elements from a delimited string: ALTER Function. To accomplish this in SQL Server - we can use charindex and substring, or you can use a combination of splitting the string to a table, and rolling the values back up in a GROUP BY. Unless I am mistaken - split_part parses a delimited string into component elements and returns the desired element from that string. | 42 | Cherry, Banana | Red,Yellow | NULL | NULL | | ID | Name | Color | LogDate | Unknown | , FIRSTROW = 2 - real data starts on the 2nd row SQL Server allows to query *.csv file as a virtual DB table on a file system. Output - using POSTGre db but require same for sql server select split_part(p.Lines, ',',1) As Col1,įrom ( select unnest(string_to_array(:csvdata, chr(10))) as Lines)p ![]() Unfortunately, sql server has a similar function STRING_SPLIT ( string, separator ) but notice it does NOT have the position aspect SPLIT_PART(string, delimiter, position) offered in POSTGre. This can be done in POSTGRE using a simple function split_part because that function includes the ability to search the csv concate string for the position of the field delimiter example ',' comma separator. ![]() ![]() To provide some context, I need to wire up this code in a third party reporting engine which is talking to sql server in addition to other multiple data sources such as oracle etc. I have multiple use cases whereby I need to effectively create a virtual sql server table from a csv source and I need to do it preferably using pure sql on the fly- ie not using a procedure. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |