How to get Substring in sql query

Question

I am trying to get substring from 0-7 position in an int element in table name so when I insert this

select substring(column_name,0, 6) as new_name from table_name 

I am getting

SQL Error [42883]: ERROR: function pg_catalog.substring(bigint, integer, integer) does not exist

Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Position: 8

org.postgresql.util.PSQLException: ERROR: function pg_catalog.substring(bigint, integer, integer) does not exist

Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 8

so after adding some cast to varchar(8)

select substring(varchar(8),column_name,0, 6) as new_name from table_name

getting this

SQL Error [42601]: ERROR: syntax error at or near "," Position: 28
org.postgresql.util.PSQLException: ERROR: syntax error at or near "," Position: 28

I cant figure out what am I doing wrong. Thanks.

Answer 1

You can cast the int column as varchar and then make a substring from 1 to 7

select substring(cast([your-column] as varchar(100)),1, 7)

Example:

  declare @a int ='1234567890';
  select substring(cast(@a as varchar(100)),1, 7) as res

Output :

  res 
  1234567