Replace the string in Column

column value for
rbkamal.blogspot.com

like
rbkamal(dot)blogspot(dot)com

I) select web_address from personal_details where web_address  like '%com'

II)Select Replace(T.web_address, '.', '(dot)') As web_address  From  personal_details T

III)update  personal_details set web_address = Replace(web_address, '.', '(dot)') where web_address like '%com'

Create Serial Number Column using postgresql

Create Serial Number Column

Qurey:

SELECT ROW_NUMBER()  OVER (ORDER BY  ColumnName1) As SrNo, ColumnName1,  ColumnName2 FROM  TableName

Example:

select row_number() over (order by user_nm) as slno, user_nm from user_pass

CONVERT A TABLE COLUMN(CHAR) TO ANOTHER DATA TYPE(INTEGER)

If you want to change character to integer in postgresql use the following



1) Create a function
CREATE OR REPLACE FUNCTION otc_chartoint(chartoconvert character varying)
  RETURNS integer AS
$BODY$
SELECT CASE WHEN trim($1) SIMILAR TO '[0-9]+'
        THEN CAST(trim($1) AS integer)
    ELSE NULL END;

$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;



2)Run this query
 ALTER TABLE <Table Name> ALTER COLUMN <Column Name> TYPE integer USING otc_chartoint(<Column Name>);