c# regular expression for Netezza data type

Question

I'm kind of on a time crunch for a SQL schema migration for a large project at work, and haven't ever used regular expressions in C#. So I'm looking for a quick turn around hopefully. How can I write a regular expression in C# so it finds these values in a variable that's a string. If it's easier/quicker to piece out the number, that's fine too. I just want to make sure it works for all numbers inside the parentheses.

CHARACTER VARYING(8000) ==> regex returns false because the length is <= 8000

CHARACTER VARYING(8001) ==> regex returns true because the length is > 8000

So my string would be, "CHARACTER VARYING({0})"

Do you want to find all {0} in `"CHARACTER VARYING({0})"` type lines such that `{0}` is more than a particular value? — Devendra D. Chavan, Oct 18 '12 at 04:01
If the length ("{0}") is 1 through 8000, I need to change the entire string to "varchar({0})". And if greater than 8000, then the entire string needs to be changed to "varchar(MAX)" — JustBeingHelpful, Oct 18 '12 at 04:10

Devendra D. Chavan · Accepted Answer · 2012-10-18T04:47:45.527

public static string ReplaceLength(string s, int maxLength)
{
    return Regex.Replace(s, @"CHARACTER VARYING\((?<length>\d+)\)",
            match => "varchar(" + (
                                   int.Parse(match.Groups["length"].Value) <= maxLength ? 
                                        match.Groups["length"].Value : 
                                        "MAX"
                                  ) + 
                            ")");
}

Test cases

ReplaceLength("CHARACTER VARYING(8001)", 8000); // varchar(MAX)
ReplaceLength("CHARACTER VARYING(8000)", 8000); // varchar(8000)

score 1 · Answer 2 · answered Oct 18 '12 at 03:35

1

you can get the number out using the following regular expression.

CHARACTER VARYING\((\d+)\) and grouping it. You can cast it as int in C# and do you required comparision.

answered Oct 18 '12 at 03:35

pogo

1,479
3
18
23

score 1 · Answer 3 · answered Nov 30 '12 at 14:21

1

Just wanted to add that there is an easier way to get this information from netezza.

select attname, datatype, attcolleng, name,  attlen, size, datatype, format_type, atttypid
from _V_RELATION_COLUMN col_t
    cross join _v_datatype dat_t
Where dat_t.objid = col_t.atttypid 
    and name='<table_name>'
    and size ='var'

In the result set you should ref the column attcolleng, which is set the length you are looking for.

answered Nov 30 '12 at 14:21

John Babb

931
10
19

That's exactly what I developed for the tool I built. "SELECT ATTNAME,FORMAT_TYPE,ATTLEN,ATTNOTNULL,ATTNUM FROM _v_relation_column WHERE NAME = " + tableName. There were 4 data types I had to use this regular expression logic for: CHARACTER VARYING, CHARACTER, NATIONAL CHARACTER VARYING and NUMBERIC. There may have been others, but those were the data types being used in our system – JustBeingHelpful Nov 30 '12 at 21:58
and in order to allow T-SQL columns to accept nulls, I checked the ATTNOTNULL to determine the correct DDL metadata for the create table command. – JustBeingHelpful Nov 30 '12 at 22:01
1

All good points. That said, if you look at the query it has an additional join on _v_datatype that has the additional data about each column. I used this view to return the length and size of the columns too. If you're interested, run the query and see for yourself. The field set returned would not require additional regex work. – John Babb Dec 31 '12 at 11:59

c# regular expression for Netezza data type

3 Answers3