Rollback to Revision 1

Source Link

edited Jun 2, 2021 at 7:52

355.8k
42
735
1.1k

I'm trying to convert a sample database file from MS SQL Server to PostgreSQL - there's a sample table at the bottom of this question.

So, I'm having two small niggles that I can't resolve. This is a sample line from the database file (see the fiddle here - you can alternatve between SQL Server and PostgreSQL):

===============p.s. thought this would be trivial.

Sample table - please see the fiddle here - you can alternate between SQL Server (input) and PostgreSQL (output)Have data like this:

[pol@fedora data]$ more tab.sql 

CREATE TABLE employee
(
  empid           INT          NOT NULL IDENTITY,
  lastname        NVARCHAR(20) NOT NULL,
  firstname       NVARCHAR(10) NOT NULL,
  title           NVARCHAR(30) NULL,
  titleofcourtesy NVARCHAR(25) NULL,
  birthdate       DATE         NOT NULL,
  hiredate        DATE         NOT NULL,
  address         NVARCHAR(60) NOT NULL,
  city            NVARCHAR(15) NOT NULL,
  region         N'Strada NVARCHAR(15)Provinciale NULL1234',
  postalcode      NVARCHAR(10)N'Reggio NULLEmilia',
  country         NVARCHAR(15) NOT NULL,
  phone           NVARCHAR(24) NOT NULLN'10289',
  mgrid           INT          NULL

N'Italy');

Desired outputI want to change the N' into just plain apostrophe ' (the N' is a SQL Server thing) but I don't want to change the NULL into the empty string, or worse ULL - so I tried:

CREATE TABLE employee
(
  empid           INT           GENERATED BY DEFAULT AS IDENTITY,
  lastname        TEXT NOT NULL CHECK (LENGTH(lastname) <= 20),
  firstname       TEXT NOT NULL CHECK (LENGTH(firstname) <=  10),
  title           TEXT     NULL,           --[pol@fedora paydata]$ attentionsed to's/N\'\'/g theseTSQLV5.sql two!
  titleofcourtesy TEXT     NULL,

but get

sed: --          "

  birthdate       DATE NOT NULL,
  hiredate        DATE NOT NULL,
  
  address         TEXT NOT NULL CHECK (LENGTH(address) <= 60),
  city            TEXT NOT NULL CHECK (LENGTH(city) <= 15),
  region          TEXT     NULL CHECK (LENGTH(region) <= 15),
  postalcode      TEXT     NULL CHECK (LENGTH(postalcode) <= 10),
  country         TEXT NOT NULL CHECK (LENGTH(country) <=  15),
  
  phone           TEXT NOT NULL CHECK (LENGTH(phone) <= 24),
  mgrid           INT    e expression NULL#1,
  test_field      TEXT     NULL CHECK (LENGTH(test_field) <= 25)char 7: 
 unterminated `s' 
);command

I know that I've used sed a lot, but would be open to any awk commands that could perform the tasks required.

I'm trying to convert a sample database file from MS SQL Server to PostgreSQL - there's a sample table at the bottom of this question.

So, I'm having two small niggles that I can't resolve. This is a sample line from the database file (see the fiddle here - you can alternatve between SQL Server and PostgreSQL):

===============

Sample table - please see the fiddle here - you can alternate between SQL Server (input) and PostgreSQL (output):

[pol@fedora data]$ more tab.sql 

CREATE TABLE employee
(
  empid           INT          NOT NULL IDENTITY,
  lastname        NVARCHAR(20) NOT NULL,
  firstname       NVARCHAR(10) NOT NULL,
  title           NVARCHAR(30) NULL,
  titleofcourtesy NVARCHAR(25) NULL,
  birthdate       DATE         NOT NULL,
  hiredate        DATE         NOT NULL,
  address         NVARCHAR(60) NOT NULL,
  city            NVARCHAR(15) NOT NULL,
  region          NVARCHAR(15) NULL,
  postalcode      NVARCHAR(10) NULL,
  country         NVARCHAR(15) NOT NULL,
  phone           NVARCHAR(24) NOT NULL,
  mgrid           INT          NULL

);

Desired output:

CREATE TABLE employee
(
  empid           INT           GENERATED BY DEFAULT AS IDENTITY,
  lastname        TEXT NOT NULL CHECK (LENGTH(lastname) <= 20),
  firstname       TEXT NOT NULL CHECK (LENGTH(firstname) <=  10),
  title           TEXT     NULL,           -- pay attention to these two!
  titleofcourtesy TEXT     NULL,           --          "

  birthdate       DATE NOT NULL,
  hiredate        DATE NOT NULL,
  
  address         TEXT NOT NULL CHECK (LENGTH(address) <= 60),
  city            TEXT NOT NULL CHECK (LENGTH(city) <= 15),
  region          TEXT     NULL CHECK (LENGTH(region) <= 15),
  postalcode      TEXT     NULL CHECK (LENGTH(postalcode) <= 10),
  country         TEXT NOT NULL CHECK (LENGTH(country) <=  15),
  
  phone           TEXT NOT NULL CHECK (LENGTH(phone) <= 24),
  mgrid           INT      NULL,
  test_field      TEXT     NULL CHECK (LENGTH(test_field) <= 25)  
   
);

I'm trying to convert a sample database file from MS SQL Server to PostgreSQL.

So, I'm having two small niggles that I can't resolve.

p.s. thought this would be trivial.

Have data like this:

N'Strada Provinciale 1234', N'Reggio Emilia', NULL, N'10289', N'Italy');

I want to change the N' into just plain apostrophe ' (the N' is a SQL Server thing) but I don't want to change the NULL into the empty string, or worse ULL - so I tried:

[pol@fedora data]$ sed 's/N\'\'/g TSQLV5.sql

but get

sed: -e expression #1, char 7: unterminated `s' command

I know that I've used sed a lot, but would be open to any awk commands that could perform the tasks required.

added 1523 characters in body

Source Link

edited Jun 1, 2021 at 18:28

Vérace

601
1
8
20

I'm trying to convert a sample database file from MS SQL Server to PostgreSQL - there's a sample table at the bottom of this question.

So, I'm having two small niggles that I can't resolve. This is a sample line from the database file (see the fiddle here - you can alternatve between SQL Server and PostgreSQL):

p.s. thought this would be trivial.===============

Have data like thisSample table - please see the fiddle here - you can alternate between SQL Server (input) and PostgreSQL (output):

N'Strada[pol@fedora Provincialedata]$ 1234'more tab.sql 

CREATE TABLE employee
(
  empid           INT          NOT NULL IDENTITY, 
 N'Reggio Emilia'lastname        NVARCHAR(20) NOT NULL,
  firstname       NVARCHAR(10) NOT NULL, 
 N'10289' title           NVARCHAR(30) NULL, 
 N'Italy' titleofcourtesy NVARCHAR(25) NULL,
  birthdate       DATE         NOT NULL,
  hiredate        DATE         NOT NULL,
  address         NVARCHAR(60) NOT NULL,
  city            NVARCHAR(15) NOT NULL,
  region          NVARCHAR(15) NULL,
  postalcode      NVARCHAR(10) NULL,
  country         NVARCHAR(15) NOT NULL,
  phone           NVARCHAR(24) NOT NULL,
  mgrid           INT          NULL

);

I want to change the N' into just plain apostrophe ' (the N' is a SQL Server thing) but I don't want to change the NULL into the empty string, or worse ULL - so I triedDesired output:

[pol@fedoraCREATE data]$TABLE sedemployee
(
 's/N\'\'/g TSQLV5.sqlempid           INT           GENERATED BY DEFAULT AS IDENTITY,

but get

sed:  lastname        TEXT NOT NULL CHECK (LENGTH(lastname) <= 20),
  firstname       TEXT NOT NULL CHECK (LENGTH(firstname) <=  10),
  title           TEXT     NULL,           -e- expressionpay #1attention to these two!
  titleofcourtesy TEXT     NULL, char 7: unterminated `s' command      --          "

  birthdate       DATE NOT NULL,
  hiredate        DATE NOT NULL,
  
  address         TEXT NOT NULL CHECK (LENGTH(address) <= 60),
  city            TEXT NOT NULL CHECK (LENGTH(city) <= 15),
  region          TEXT     NULL CHECK (LENGTH(region) <= 15),
  postalcode      TEXT     NULL CHECK (LENGTH(postalcode) <= 10),
  country         TEXT NOT NULL CHECK (LENGTH(country) <=  15),
  
  phone           TEXT NOT NULL CHECK (LENGTH(phone) <= 24),
  mgrid           INT      NULL,
  test_field      TEXT     NULL CHECK (LENGTH(test_field) <= 25)  
  
);

I know that I've used sed a lot, but would be open to any awk commands that could perform the tasks required.

I'm trying to convert a sample database file from MS SQL Server to PostgreSQL.

So, I'm having two small niggles that I can't resolve.

p.s. thought this would be trivial.

Have data like this:

N'Strada Provinciale 1234', N'Reggio Emilia', NULL, N'10289', N'Italy');

I want to change the N' into just plain apostrophe ' (the N' is a SQL Server thing) but I don't want to change the NULL into the empty string, or worse ULL - so I tried:

[pol@fedora data]$ sed 's/N\'\'/g TSQLV5.sql

but get

sed: -e expression #1, char 7: unterminated `s' command

I know that I've used sed a lot, but would be open to any awk commands that could perform the tasks required.

I'm trying to convert a sample database file from MS SQL Server to PostgreSQL - there's a sample table at the bottom of this question.

So, I'm having two small niggles that I can't resolve. This is a sample line from the database file (see the fiddle here - you can alternatve between SQL Server and PostgreSQL):

===============

Sample table - please see the fiddle here - you can alternate between SQL Server (input) and PostgreSQL (output):

[pol@fedora data]$ more tab.sql 

CREATE TABLE employee
(
  empid           INT          NOT NULL IDENTITY, 
  lastname        NVARCHAR(20) NOT NULL,
  firstname       NVARCHAR(10) NOT NULL, 
  title           NVARCHAR(30) NULL, 
  titleofcourtesy NVARCHAR(25) NULL,
  birthdate       DATE         NOT NULL,
  hiredate        DATE         NOT NULL,
  address         NVARCHAR(60) NOT NULL,
  city            NVARCHAR(15) NOT NULL,
  region          NVARCHAR(15) NULL,
  postalcode      NVARCHAR(10) NULL,
  country         NVARCHAR(15) NOT NULL,
  phone           NVARCHAR(24) NOT NULL,
  mgrid           INT          NULL

);

Desired output:

CREATE TABLE employee
(
  empid           INT           GENERATED BY DEFAULT AS IDENTITY,
  lastname        TEXT NOT NULL CHECK (LENGTH(lastname) <= 20),
  firstname       TEXT NOT NULL CHECK (LENGTH(firstname) <=  10),
  title           TEXT     NULL,           -- pay attention to these two!
  titleofcourtesy TEXT     NULL,           --          "

  birthdate       DATE NOT NULL,
  hiredate        DATE NOT NULL,
  
  address         TEXT NOT NULL CHECK (LENGTH(address) <= 60),
  city            TEXT NOT NULL CHECK (LENGTH(city) <= 15),
  region          TEXT     NULL CHECK (LENGTH(region) <= 15),
  postalcode      TEXT     NULL CHECK (LENGTH(postalcode) <= 10),
  country         TEXT NOT NULL CHECK (LENGTH(country) <=  15),
  
  phone           TEXT NOT NULL CHECK (LENGTH(phone) <= 24),
  mgrid           INT      NULL,
  test_field      TEXT     NULL CHECK (LENGTH(test_field) <= 25)  
  
);

Source Link

asked Jun 1, 2021 at 14:27

Vérace

601
1
8
20

Regular expression - SQL manipulation

[pol@fedora data]$ lsb_release -a
LSB Version:    :core-4.1-amd64:core-4.1-noarch
Distributor ID: Fedora
Description:    Fedora release 34 (Thirty Four)
Release:    34
Codename:   ThirtyFour

I'm trying to convert a sample database file from MS SQL Server to PostgreSQL.

So, I'm having two small niggles that I can't resolve.

shipname       NVARCHAR(40) NOT NULL,

That's

(always) two spaces
identifier (i.e. field name) - always [a-z] - lower case alphabetical
followed by an unknown number of spaces
followed by NVARCHAR(xy) NOT NULL or it may be followed by NVARCHAR(xy) NULL

and I want to transform this into

shipname       TEXT NOT NULL CHECK (LENGTH(shipname)  <= xy),

or

shipname       TEXT NULL,

What I have so far:

sed 's/^  [a-z]+[ ]+NVARCHAR([0-9]+) NOT NULL/TEXT NOT NULL CHECK \(LENGTH\((\1) <= (\2)\)/g'

So,

^ is the beginning of the string
followed by two spaces
followed by my field name [a-z]+
followed by an arbitrary no. of spaces [ ]+
NVARCHAR([0-9]+)

and substitute in

TEXT followed by NOT NULL then CHECK(LENGTH(xy) - back reference 1 - <= back reference 2...

I've tried various permutations and combinations of the above, but nothing appears to work for me.

[pol@fedora data]$ sed 's/^  [a-z]+[ ]+NVARCHAR([0-9]+) NOT NULL/TEXT NOT NULL CHECK \(LENGTH\((\1) <= (\2)\)/g' 
sed: -e expression #1, char 87: invalid reference \2 on `s' command's RHS

Get invalid back reference...

Ideally, and I stress ideally, if the string following NVARCHAR(xy) is NULL and not NOT NULL, I don't want any length check - because it doesn't make sense to take the LENGTH of a NULL... this is conditional behaviour - not sure if it's possible in regexps....

p.s. thought this would be trivial.

Have data like this:

N'Strada Provinciale 1234', N'Reggio Emilia', NULL, N'10289', N'Italy');

I want to change the N' into just plain apostrophe ' (the N' is a SQL Server thing) but I don't want to change the NULL into the empty string, or worse ULL - so I tried:

[pol@fedora data]$ sed 's/N\'\'/g TSQLV5.sql

but get

sed: -e expression #1, char 7: unterminated `s' command

I know that I've used sed a lot, but would be open to any awk commands that could perform the tasks required.

Stack Exchange Network

Return to Question

Regular expression - SQL manipulation