Is following query is correct for postgresql? How can we yse multiple substring in single query?

Question

1.00/5 (1 vote)

See more:

SELECT product_id, 
SUBSTRING (product_id FROM 0 FOR 3) AS First_Part,
SUBSTRING (product_id FROM 5 FOR 2) AS Second_Part,
SUBSTRING (product_id FROM 8 FOR 8) AS Last_Part
FROM product;

What I have tried:

I used this query to get the product ID in 3 parts.

But only in first row I am not getting expected results, but the data in rest of the rows are correct.

I am using PGADMIN

can someone check and please help me.

Posted 13-May-23 22:13pm

Om Language Studio German Language House

Updated 15-May-23 9:18am

Add a Solution

Comments

OriginalGriff 14-May-23 4:18am

Without any idea what your data looks like, what you are getting as a result, and what you expect to get, we can't help you.

Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with - we get no other context for your project.

Use the "Improve question" widget to edit your question and provide better information.

Member 15627495 14-May-23 7:11am

substring first char is at 1 for start index.

SELECT product_id, 
SUBSTRING(product_id,1,3) AS 'First_Part',
SUBSTRING(product_id,6,2) AS 'Second_Part',
SUBSTRING(product_id,9,9) AS 'Last_Part'
FROM product

by this query, the result will be 4 columns.
when you're stuck, or on a faulty query, step your query build, it's to debug slowly.
by starting at beginning, executing the query as its length increase.

Someone can say : " delete all your query, write the first column only select, execute the query, and check if it's well wwritten and if the result is good.
after this, make the query with two columns, check the result .. until you have the await Result.

Wendelius 14-May-23 11:52am

Try posting the original data in the table and an example what the query should return. Without this info there's not much we can do

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2023-05-15T09:17:00

Solution 1

Accordingly to the documentation (PostgreSQL: Documentation: 9.1: String Functions and Operators[^]):

SQL

substring('Thomas' from 2 for 3)
--returns: hom

Conclusion: your query is incorrect.

Posted 15-May-23 9:17am

Maciej Los