I want to split this string "name#email#mobile#country" using #, and insert those 4 values into a table.
The table columns are: id, name, email, mobile, country
I have to make it using SQL, not from any programming language.
-
2Probably better to do that in a layer above. And use parameters.jarlh– jarlh2017-08-28 08:06:09 +00:00Commented Aug 28, 2017 at 8:06
-
i am new to sql query. don't know how toS R RAIN– S R RAIN2017-08-28 08:07:44 +00:00Commented Aug 28, 2017 at 8:07
-
1What database server are you using? @jarlh is right, you could do that in application rather than in sqlCid– Cid2017-08-28 08:08:25 +00:00Commented Aug 28, 2017 at 8:08
-
Which DBMS are you using? Postgres? Oracle?user330315– user3303152017-08-28 09:11:47 +00:00Commented Aug 28, 2017 at 9:11
-
Please Edit your question and add the table definition of the table you want to insert into and tell us which programming language you are using. Formatted text please, no screen shots. edit your question - do not post code or additional information in comments.user330315– user3303152017-08-28 09:13:22 +00:00Commented Aug 28, 2017 at 9:13
|
Show 2 more comments
1 Answer
Let's check the link below. I think it will help you well
This SQL will split a string to columns based on #
declare @str varchar(100)
set @str = 'name123#email123#mobile123#country123'
SELECT @str,
substring(@str, 1, charindex('#',@str) - 1)'name',
substring(@str, charindex('#',@str) + 1, charindex('#',@str, charindex('#',@str) + 1) - charindex('#',@str) - 1) 'email',
substring(@str, charindex('#',@str, charindex('#',@str) + 1) + 1, charindex('#',@str, charindex('#',@str, charindex('#',@str) + 1) + 1) - charindex('#',@str, charindex('#',@str) + 1) - 1)'mobile',
substring(@str, charindex('#',@str, charindex('#',@str, charindex('#',@str) + 1) + 1) + 1, len(@str)) 'country'
5 Comments
Rahul
Basically, MySQL and SQL Server are the same what you mean?
Hieu Le
@Rahul, I mean in my link it's SQL Server. But you can convert it to MySQL stored procedure quickly. Because it's same logic.
S R RAIN
I can split the string but i cant insert those like from my given string after split i got this---- name#email#mobile#country 1 - name 2 - email 3 - mobile 4 - country but from this output i cant insert those in to my table
Hieu Le
@SRRAIN I updated my answer based on your updated question